URGENT help with statement please

  • HI

    Can someone give me a hand solving a statement for solving the described scenarion:

    I have a table containing an ID field and a Transaction field

    ID              Transaction

    --              ------------

    1               1000

    1               2000

    1               3000

    2               2000

     

    From this table I want get a list of the ID's that has exactely these transactions..i.e they must have both these transaction.

    To be able to retrive this I can NOT do:

    select * from table where transaction = '1000' or transaction='2000' since this will also return ID=2 that only has one of the transaction..

    If I use AND clause like this:

    select * from table where transaction = '1000' AND transaction='2000'

    ...I will not receive anything...

    Suggestions appricated..how can I get only the ID's with all the transactions I want, and only the ID's that have both these listed transaction ????

    BR

    John O'Connor

  • Select * from tbl

     where ID IN (SELECT ID FROM tbl WHERE transaction='1000')

     AND ID IN (SELECT ID FROM tbl WHERE transaction='2000')

    Messy, but it'll work

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Please explain with the output (Example) that actually wants to list...

     

     

  • Thanks for the answer..I just realized I missed to give some input...I want to dynamically build up the Where clause, that is  - in the end I want to put this complete SQL statement in a stored procedure, then, as an input string to the procedure I want to give the Where clause or a similar string that I can use as a filter in my statement...for example the  input string could look like:

    @inStr = 'transaction=1000 OR transaction=2000'

    Can someone reckon how I based upon this approach can get the ID's I want to ?

    Best Regards

    John

     

  • Venkat, the ouput should for the above example be:

     

    ID

    --

    1

     

    Since the ID=1 is the only ID that matches both my transaction types (1000 and 2000)....Note that the filter for the transaction types must be able to be built up dynamically, since this will be passed as a string (in some way) to a stored procedure..

     

    John

  • John - if you do this you'll get your IDs....

    select id from myTable where transactionType in (1000, 2000)

    group by id having count(*) = 2

    However, to pass a dynamic filter, follow this link and look at remi's solution...

    remi/RGR'Us split function







    **ASCII stupid question, get a stupid ANSI !!!**

  • 'The Dreaded Dynamic SQL Syndrome'

    DECLARE @sql nvarchar(4000),@inStr nvarchar(2000)

    SET @inStr = '[Transaction]=1000 OR [Transaction]=2000'

    SET @sql = '

    SELECT [ID]

    FROM

    WHERE ' + @instr + '

    GROUP BY [ID]

    HAVING COUNT(DISTINCT [Transaction]) = ' +

    CAST((LEN(@instr)-LEN(REPLACE(@instr,'[Transaction]','')))/13 as varchar)

    EXEC(@sql)

    or if you do as sushila suggests

    and put the list of transactions in a temp table then

    DECLARE @trancount int

    SELECT @trancount = COUNT(*) FROM [listtable]

    SELECT [ID]

    FROM

    a

    INNER JOIN [listtable] b ON b.[Transaction] = a.[Transaction]

    GROUP BY [ID]

    HAVING COUNT(DISTINCT [Transaction]) = @trancount

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Thanks a lot for the input....just a small additional question...why the division by 13 ??

    HAVING COUNT(DISTINCT [Transaction]) = ' +

    CAST((LEN(@instr)-LEN(REPLACE(@instr,'[Transaction]','')))/13 as varchar)

     

    John

  • Because '[Transaction]' is 13 chars long and the difference between the string with and without all '[Transaction]' is 26 (in this case) and divided by 13 gives 2 (the number of codes being looked for)

    This is needed for the equality check on the count to only find the ID's with all the Transaction codes present

    I did it this way in case the where clause had other tests other than Transaction, obviously this will not work if you match Transaction on anything but = (eg Transaction>=1000 AND Transaction<=2000)

     

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Why not INSERT the transaction numbers you want to retrieve into a #Temp or @Table first and avoid the dynamic sql and then select the records from the main table where they are IN the temp table???

Viewing 10 posts - 1 through 9 (of 9 total)

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