URGENT help with statement please

Question

Post reply

URGENT help with statement please

JonkoLainen

SSC Journeyman

Points: 97
More actions
November 15, 2005 at 6:02 am

#92247

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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

venkat-216866 SSC Veteran Points: 257 More actions · Answer 2

Please explain with the output (Example) that actually wants to list...

JonkoLainen SSC Journeyman Points: 97 More actions · Answer 3

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

JonkoLainen SSC Journeyman Points: 97 More actions · Answer 4

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

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

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 !!!**

David Burrows SSC Guru Points: 65142 More actions · Answer 6

'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.

JonkoLainen SSC Journeyman Points: 97 More actions · Answer 7

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

David Burrows SSC Guru Points: 65142 More actions · Answer 8

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.

dougjjj SSCrazy Points: 2081 More actions · Answer 9

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???