Miguel Miguel - 1 month ago 5
SQL Question

SQL for select id and childrens for example in ( 1,2 ) and then select courses for this 2 categories

I have my 2 tables like this and I want to select course for categories table where id, i.e 1 , also with courses that has parent 1 as category.

I try something like:

SELECT id,category,( (SELECT id FROM courses WHERE id = 1 OR parent = 1) AS selection) FROM courses
WHERE category IN selection;


But is not a right syntax, and also I would say I miss puttting into string comma separated to do something like that.

table categories

+----+--------+------+
| id | parent | path |
+----+--------+------+
| 1 | 0 | /1 |
| 2 | 1 | /1/2 |
+----+--------+------+


table courses

+----+----------+-----------+
| id | category | shortname |
+----+----------+-----------+
| 1 | 0 | MB2.9.8 |
| 2 | 2 | 1 c |
| 3 | 2 | 2 c |
| 4 | 1 | c 3 |
+----+----------+-----------+


Which means I would have this when I just send category id = 1 , because categories.parent on id "2" has "1" as parent too so selection of courses for 1 and 2 categories

+----+----------+-----------+
| id | category | shortname |
+----+----------+-----------+
| 2 | 2 | 1 c |
| 3 | 2 | 2 c |
| 4 | 1 | c 3 |
+----+----------+-----------+

Answer

You need a join

select CO.*
from Courses CO
inner join Categories CA
    on CA.Category = CO.Id
    or CA.Category = CO.Parent
where CA.ID = 1
or CA.Parent = 1

or an EXISTS:

select CO.*
from Courses CO
where exists (
    select Id 
    from Category CA
    where (CA.Parent = CO.Category
    or CA.Id = CO.Category)
    and (CA.ID = 1
    or CA.Parent = 1)
    )
Comments