I have a SQL table like this:

ID,     ID2,      category.   date,     txt
 1      null         1        Y-m-d     text
 2      null         1        Y-m-d     text
 3      4            1        Y-m-d     text
 4      null         2        Y-m-d     text
 5      6            1        Y-m-d     text
 6      null         3        Y-m-d     text
 7      null         5        Y-m-d     text

I try to select all rows with category = 1 like id: 1,2,3,5,6, but also I want row with id 4,6 even if their category is not 1, but they are in ID2 of a row that have category 1.

Select * from table WHERE category=1 AND ....  LIMIT 20

So results will be 1,2,3,4,5,6

CodePudding user response：

Use a self join here:

SELECT t1.*
FROM yourTable t1
LEFT JOIN yourTable t2
    ON t2.ID2 = t1.ID
WHERE
    t1.category = 1 OR t2.category = 1;

Demo

CodePudding user response：

You can use exists

select *
from t
where category=1
  or exists (select * from t t2 where t2.id2=t.id and t2.category=1)

CodePudding user response：

how you want the result?

first:

select id t from test a where a.category='1' and id is not null

union all

select id2 t from test a where a.category='1' and id2 is not null

second:

select a.* from test a left join test b on a.id=b.id where a.category='1' or b.category='1'