SQL query

  • CREATE TABLE #holding(

    [TIDM] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [CLIENTCODE] int NULL ,

    ) ON [PRIMARY]

    INSERT #holding

    (TIDM,CLIENTCODE)

    values ('ABC',1234),

    ('BCD',1234),

    ('BCD',1234),

    ('LMF',1234),

    ('DEC',1234),

    ('ADE',1234),

    ('LGJ',1234),

    ('PCD',1234),

    ('ABC',1234)

    so i want a list of clientcode who dont hold a particular TIDM.

    for eg if I my search parameter is ABC then the result should be empty row. However if my parameter is YYY then the result should be only one row which should have account number 1234.

  • freecoder (11/26/2012)


    CREATE TABLE #holding(

    [TIDM] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [CLIENTCODE] int NULL ,

    ) ON [PRIMARY]

    INSERT #holding

    (TIDM,CLIENTCODE)

    values ('ABC',1234),

    ('BCD',1234),

    ('BCD',1234),

    ('LMF',1234),

    ('DEC',1234),

    ('ADE',1234),

    ('LGJ',1234),

    ('PCD',1234),

    ('ABC',1234)

    so i want a list of clientcode who dont hold a particular TIDM.

    for eg if I my search parameter is ABC then the result should be empty row. However if my parameter is YYY then the result should be only one row which should have account number 1234.

    Nice job posting ddl and sample data. Your description however is very unclear. So if there are rows you want none returned and if there are no rows you want one returned? The first part of that is simple. The second part is not so simple unless the account number 1234 is hardcoded.

    This works according to your sample data and description.

    declare @Parm char(3) = 'ABC'

    --set @Parm = 'YYY' --uncomment this for the YYY value

    select top 1 1234

    from #holding

    where not exists(select * from #holding where TIDM = @Parm)

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Another option, based on your description.

    DECLARE @searchParam VARCHAR(9) = 'YYY';

    SELECT CLIENTCODE

    FROM (SELECT CLIENTCODE, CASE WHEN MAX(b.TIDM) IS NULL THEN 1 ELSE 0 END

    FROM #holding a

    LEFT OUTER JOIN (VALUES(@searchParam)) b(TIDM) ON a.TIDM = b.TIDM

    GROUP BY CLIENTCODE

    ) a(CLIENTCODE, display)

    WHERE a.display = 1;


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • sorry if i was unclear.

    basically I am writing a stored procedure for a user to check if a certain clientcode does not hold a certain tidm.

    CREATE TABLE #holding(

    [TIDM] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [CLIENTCODE] int NULL ,

    ) ON [PRIMARY]

    INSERT #holding

    (TIDM,CLIENTCODE)

    values ('ABC',1234),

    ('BCD',1234),

    ('BCD',1234),

    ('LMF',1234),

    ('DEC',1234),

    ('ADE',1234),

    ('LGJ',1234),

    ('PCD',1234),

    ('ABC',1222),

    ('ACC',1254)

    ('ADC',1274)

    ('LLJ',1234)

    ('KMC',1234)

    A user can search both with tidm and clientcode

    If the search parameter @tidm is ' ' and @clientcode = ' ' then I want to display all the results

    If the search parameter @tidm is 'ABC' and @clientcode = ' ' then I want to display the clientcode who dont have ABC tidm. So in this case the result should exclude 1234 and 1222 and display rest of the client codes to tell the user that those clientcodes does not have ABC tidm.

    if @tidm = 'ABC' and @clientcode = '1234' then the result should be everthing excluding the the rows with clientcode 1234.

  • freecoder (11/26/2012)


    sorry if i was unclear.

    basically I am writing a stored procedure for a user to check if a certain clientcode does not hold a certain tidm.

    A user can search both with tidm and clientcode

    If the search parameter @tidm is ' ' and @clientcode = ' ' then I want to display all the results

    If the search parameter @tidm is 'ABC' and @clientcode = ' ' then I want to display the clientcode who dont have ABC tidm. So in this case the result should exclude 1234 and 1222 and display rest of the client codes to tell the user that those clientcodes does not have ABC tidm.

    if @tidm = 'ABC' and @clientcode = '1234' then the result should be everthing excluding the the rows with clientcode 1234.

    Other than lacking a "@clientcode" variable, the code I supplied you with appears to do what you've requested. Are you struggling to edit it to suit your purposes?


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply