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.
下面是我使用的SQL查询，它是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.
下面是O/P。
ID | CID
3 | 4
4 | 0
But what I want as an O/P is 1,3,4,6.
但我想要的是，O/P是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.
O/P的逻辑是正在使用的行id或CIDs。如果我解释更多，当ID是1 CID 3，当ID是3 CID是4，当ID是6 CID是3。当我得到在表格中使用的唯一id和CIDs时，会是1 3 4 6。
Final Correct O/P Required is below.
最终正确的O/P要求如下。
ID
1
3
4
6
How can I get it done?
我该怎么做呢?
3 个解决方案
#1
1
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:
不知道你想做什么。我认为你是说你想要有非零CID的行ID或者是CID列所引用的。(?)试试这个:
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
#2
1
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
#3
1
I think this may be what you want:
我想这可能就是你想要的:
select ID
from tbl_a
where id in (3, 4) or cid in (3, 4);
#1
1
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:
不知道你想做什么。我认为你是说你想要有非零CID的行ID或者是CID列所引用的。(?)试试这个:
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
#2
1
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
#3
1
I think this may be what you want:
我想这可能就是你想要的:
select ID
from tbl_a
where id in (3, 4) or cid in (3, 4);