Mysql：只有子表有行时才从父表中选择

Question

I need your help, How do I Select from a parent table, only if child table (another table that depends on id of parent table) has rows in php?

For instance: I have CATEGORY table and Items table. Where Category is the parent table and contains ct_id, ct_name, while Items is child table which contains it_id, ct_id (linked to parent table), it_name

PS: I don't need to select items, but I need only Categories ONLY if there are items linked to this table.

Thank you

Answer 1

You could use INNER JOIN which only returns records when there is a match on both tables.

SELECT DISTINCT a.* FROM Categories a INNER JOIN Items b on b.ct_id = a.ct_id

But it is more efficient to run a subquery:

SELECT *
FROM Categories 
WHERE ct_id IN (SELECT ct_id FROM Items);

This is because in the first example it has to match the entire table first and then strip out all the duplicates using the DISTINCT keyword. The second example avoids the duplication by scanning the child table first.

This is known as a Semi Join. See here for more: https://dev.mysql.com/doc/refman/5.6/en/semi-joins.html