locked
In Select query how to separate particular row RRS feed

  • Question

  • User-582711651 posted

    Hi,

    This is SQL table output  (Select * from table)

    E_CODE ATTD_DT L_ID L_DETLS L_Exist Balance
    118 6/20/2015 1 CL 0 0.5
    118 6/20/2015 2 CLH 0 0.5
    118 6/20/2015 3 SL 0 24
    118 6/20/2015 4 SLH 0 24
    118 6/20/2015 5 EL 0 36
    118 6/20/2015 8 MAL 0 10
    118 6/20/2015 9 COL 0 2
    118 6/20/2015 10 PL 0 10

    Condition 1:

    When Balance = 0.5 then need to remove L_ID =1 in same row 

    For ex. Wants to see like this

    E_CODE ATTD_DT L_ID L_DETLS L_Exist Balance
    118 6/20/2015 2 CLH 0 0.5
    118 6/20/2015 3 SL 0 24
    118 6/20/2015 4 SLH 0 24
    118 6/20/2015 5 EL 0 36
    118 6/20/2015 8 MAL 0 10
    118 6/20/2015 9 COL 0 2
    118 6/20/2015 10 PL 0 10

    But we try to set conditions in Select, or Joins only 

    My Table here,

    USE [MG_School]
    GO
    /****** Object:  Table [dbo].[TEMP]    Script Date: 08/13/2015 09:17:12 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[TEMP](
    	[E_CODE] [int] NULL,
    	[ATTD_DT] [date] NULL,
    	[L_ID] [int] NOT NULL,
    	[L_DETLS] [varchar](5) NOT NULL,
    	[L_Exist] [int] NOT NULL,
    	[Balance] [numeric](38, 2) NULL
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    INSERT [dbo].[TEMP] ([E_CODE], [ATTD_DT], [L_ID], [L_DETLS], [L_Exist], [Balance]) VALUES (118, CAST(0x183A0B00 AS Date), 1, N'CL', 0, CAST(0.50 AS Numeric(38, 2)))
    INSERT [dbo].[TEMP] ([E_CODE], [ATTD_DT], [L_ID], [L_DETLS], [L_Exist], [Balance]) VALUES (118, CAST(0x183A0B00 AS Date), 2, N'CLH', 0, CAST(0.50 AS Numeric(38, 2)))
    INSERT [dbo].[TEMP] ([E_CODE], [ATTD_DT], [L_ID], [L_DETLS], [L_Exist], [Balance]) VALUES (118, CAST(0x183A0B00 AS Date), 3, N'SL', 0, CAST(24.00 AS Numeric(38, 2)))
    INSERT [dbo].[TEMP] ([E_CODE], [ATTD_DT], [L_ID], [L_DETLS], [L_Exist], [Balance]) VALUES (118, CAST(0x183A0B00 AS Date), 4, N'SLH', 0, CAST(24.00 AS Numeric(38, 2)))
    INSERT [dbo].[TEMP] ([E_CODE], [ATTD_DT], [L_ID], [L_DETLS], [L_Exist], [Balance]) VALUES (118, CAST(0x183A0B00 AS Date), 5, N'EL', 0, CAST(36.00 AS Numeric(38, 2)))
    INSERT [dbo].[TEMP] ([E_CODE], [ATTD_DT], [L_ID], [L_DETLS], [L_Exist], [Balance]) VALUES (118, CAST(0x183A0B00 AS Date), 8, N'MAL', 0, CAST(10.00 AS Numeric(38, 2)))
    INSERT [dbo].[TEMP] ([E_CODE], [ATTD_DT], [L_ID], [L_DETLS], [L_Exist], [Balance]) VALUES (118, CAST(0x183A0B00 AS Date), 9, N'COL', 0, CAST(2.00 AS Numeric(38, 2)))
    INSERT [dbo].[TEMP] ([E_CODE], [ATTD_DT], [L_ID], [L_DETLS], [L_Exist], [Balance]) VALUES (118, CAST(0x183A0B00 AS Date), 10, N'PL', 0, CAST(10.00 AS Numeric(38, 2)))
    

    Thanks in advance

      

    Thursday, August 13, 2015 12:00 AM

Answers

  • User61956409 posted

    Hi ayyappan.CNN,

    ayyappan.CNN

    Condition 1:

    When Balance = 0.5 then need to remove L_ID =1 in same row 

    Please try the following query string to exclude a specific row.

    select * from [yourtable]
    where [E_CODE] not in
    (
    select [E_CODE] from [yourtable]
    where [L_ID]=1 and [Balance]=0.5
    )
    

    UPDATED: I’m sorry, I find the [E_CODE] is not a primary key, it is not unique. So my above query string will not work.

    You could try this

    select * from [yourtable] where [[L_ID]!=1 or [Balance]!=0.5
    

    Best Regards,

    Fei Han

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 14, 2015 3:13 AM

All replies

  • User1256425868 posted

    I am little bit confused about your requierment. But what I understand you can try this.

    Select * from table where Balance >=0.5 and L_ID !=1

     

    Thursday, August 13, 2015 11:53 AM
  • User61956409 posted

    Hi ayyappan.CNN,

    ayyappan.CNN

    Condition 1:

    When Balance = 0.5 then need to remove L_ID =1 in same row 

    Please try the following query string to exclude a specific row.

    select * from [yourtable]
    where [E_CODE] not in
    (
    select [E_CODE] from [yourtable]
    where [L_ID]=1 and [Balance]=0.5
    )
    

    UPDATED: I’m sorry, I find the [E_CODE] is not a primary key, it is not unique. So my above query string will not work.

    You could try this

    select * from [yourtable] where [[L_ID]!=1 or [Balance]!=0.5
    

    Best Regards,

    Fei Han

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 14, 2015 3:13 AM