Reputation: 5226

union on the same table

I have a table:

ID | Id1 | Id2
1  | 100 | 12
2  | 196 | 140
3  | 196 | 141
4  | 150 | 140
5  | 150 | 199

I want to write a query that will give me a table containing records with the same ID2 and with id1 equal to 196 or 150. I thought about union:

select * from table where ID1 = 196 union select * from table where ID1 = 150

but that doesn't cover the ID2 requirement. How should I do that?

Upvotes: 0

Answers (4)

Reputation: 30873

If I understood your question correctly then this should be the answer:

select * from mytable where id2 in
(select id2 from mytable
group by id2
having count(*)>=2)
and (id1=196 or id1=150)

Upvotes: 1

Reputation: 1570

You can join the table to itself

 select T1.* from Mytable T1 inner join MyTable T2 
 ON T1.ID2 = T2.ID2
 AND T1.ID1 in (196, 150)

Something like that, depending on your exact requirement and table structures you may need to do a little more work to avoid duplicate entries.

Upvotes: 0

Reputation: 9389

SELECT ID
FROm yourTable as table1
INNER JOIN yourTable as table2
ON table1.Id2 = table2.Id2
WHERE table1.id1 IN (196.150)

here we go

Upvotes: 0

Reputation: 190907

Why are you doing a union?

You could accomplish the same task with in.

SELECT * FROM Table WHERE Id1 IN (196, 150) OR Id2 IN (196, 150)

Upvotes: 0