November 15, 2005 at 6:02 am
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
November 15, 2005 at 6:38 am
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
November 15, 2005 at 6:44 am
Please explain with the output (Example) that actually wants to list...
November 15, 2005 at 6:49 am
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
November 15, 2005 at 7:02 am
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
November 15, 2005 at 7:11 am
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...
**ASCII stupid question, get a stupid ANSI !!!**
November 15, 2005 at 7:20 am
'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.
November 15, 2005 at 8:11 am
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
November 15, 2005 at 8:23 am
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.
November 16, 2005 at 2:47 am
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