CODEWITHSUNDEEP
mysqlsqlself-join
Techie
Techie

Reputation: 45124

SELF JOIN Unique Resultset Issue - MySQL

I have a table as below

ID | CID

1  | 3
2  | 0
3  | 4
4  | 0
5  | 0
6  | 3

Below is the SQL query I use which is SELF JOIN.

SELECT t1.ID
FROM `tbl_a` AS t1 JOIN `tbl_a` AS t2 
ON t1.ID = t2.CID

Which gives me O/P as below.

ID | CID

3  | 4
4  | 0

But what I want as an O/P is 1,3,4,6.

Logic of the O/P is Rows IDs or CIDs which are being used. If I explain more When the ID is 1 CID 3, When the ID is 3 CID is 4, When the ID is 6 CID is 3. When I get the unique IDs & CIDs that are used in the table would be 1,3,4,6.

Final Correct O/P Required is below.

ID  

1   
3   
4   
6

How can I get it done?

Upvotes: 0

Views: 120

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I think this may be what you want:

select ID
from tbl_a
where id in (3, 4) or cid in (3, 4);

Upvotes: 1

bvr
bvr

Reputation: 4826

Try this

SELECT t2.ID
FROM `tbl_a` AS t1 JOIN `tbl_a` AS t2 
ON t1.ID = t2.CID
OR t2.ID = t1.CID
GROUP BY t2.ID

Upvotes: 1

Craig
Craig

Reputation: 3297

Not sure what you're trying to do. I think you are saying you want the ID of rows that have a non-zero CID or that are referenced by the CID column. (?) Try this:

SELECT ID FROM tbl_a AS t1 WHERE CID <> 0 OR EXISTS(SELECT * FROM tbl_a AS t2 WHERE t2.CID = t1.ID) ORDER BY ID

Upvotes: 1

Related Questions