UNION or other way to extract data?

Question

Post reply

UNION or other way to extract data?

branovuk

SSCommitted

Points: 1774
More actions
September 26, 2011 at 10:29 am

#245415

Hello,
I have one table with data:
transaction_id type
1 ____________ A
1 ____________ B
2 ____________ A
3 ____________ A
4 ____________ A
5 ____________ A
5 ____________ B
5 ____________ B
6 ____________ A
7 ____________ A
8 ____________ A
9 ____________ B
...
I have to create a query which pull (correction) A's and B's which has same transaction_id.
So, result should be:
1 ____________ A
1 ____________ B
5 ____________ A
5 ____________ B
5 ____________ B
As you can see, transaction_id 9 is not in the query, as no responding A record exists.
Thanks,
Brano

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

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

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 1

Probably:

select id, type from myTbl where id in (select id from myTbl where type ='A')

if I did not miss something

drew.allen SSC Guru Points: 76997 More actions · Answer 2

Your English statement of the criteria does not match the expected results. Transaction_IDs 1-8 all have an instance of Type A, so they should all be included in your results based on the English criteria. So, which is correct: the English statement or the expected results?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

branovuk SSCommitted Points: 1774 More actions · Answer 3

Thanks for answers, will try that one.

Drew, you are right, not all of them, query is correct, not the English statement.

Thanks,

Brano

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

Yuri55 (9/26/2011)
Probably:
select id, type from myTbl where id in (select id from myTbl where type ='A')
if I did not miss something

That will give you all the rows that has A irrespective of the presence of B. Wont match OP's expected result

Try this:

select id, type

from myTbl outertable

where exists (select innertable.id from myTbl innertable

where innertable.type ='B' and innertable.id = outertable.id )

and type = 'A'

Lowell SSC Guru Points: 323518 More actions · Answer 5

does this do what you are asking?

it seems a little round about, but it works with the sample data given.

/*

--Results

transaction_idtype

1A

1B

5A

5B

*/

With mySampleData (transaction_id,type)

AS

(

SELECT 1,'A' UNION ALL

SELECT 1,'B' UNION ALL

SELECT 2,'A' UNION ALL

SELECT 3,'A' UNION ALL

SELECT 4,'A' UNION ALL

SELECT 5,'A' UNION ALL

SELECT 5,'B' UNION ALL

SELECT 6,'A' UNION ALL

SELECT 7,'A' UNION ALL

SELECT 8,'A' UNION ALL

SELECT 9,'B')

SELECT mySampleData.* FROM mySampleData

WHERE transaction_id IN(SELECT transaction_id FROM mySampleData WHERE type = 'B')

AND transaction_id IN(SELECT transaction_id FROM mySampleData WHERE type = 'A')

order by transaction_id,type

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

drew.allen SSC Guru Points: 76997 More actions · Answer 6

This will also give you the correct results. I suspect it might be more efficient than having the two separate subqueries, but haven't had a chance to test it.

SELECT mySampleData.* FROM mySampleData

WHERE transaction_id IN(

SELECT transaction_id

FROM mySampleData

GROUP BY transaction_id

HAVING Min(type) = 'A'

AND Max(type) = 'B'

)

order by transaction_id,type

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

branovuk SSCommitted Points: 1774 More actions · Answer 7

Thank you all for answers!

Lowell, it seems your query worked.

Thanks again,

Brano

branovuk SSCommitted Points: 1774 More actions · Answer 8

Drew,

i saw your answer right now, after I post my reply.

Thank you, will work on both so I can compare results.

Nobody is happier than me :-),

Thanks,

Brano