查询连接id匹配的行和存在的行

I'm in need of a MySQL query that join several tables only if those tables have a matching ID and it actually exists in the table.

I have 1 parent table and several sub tables. The parent tables contains every row while the sub tables only contain the rows that they need. I need to join the parent table with the sub tables if the parent table has an ID that exists in one of the sub tables.

This is what I am currently using which does not work. This query ends up having no result because the sub table rows may not exist. For example, 1 row may exist in the "widgets" table and the "widget_login" table because the widget is a "login" widget, but all the other tables are empty. But it should still check all tables. I'm not sure how to overcome this.

SELECT FROM widgets
JOIN widget_login ON widget_login.id=widgets.id
JOIN widget_admin ON widget_admin.id=widgets.id
JOIN widget_facebook ON widget_facebook.id=widgets.id
WHERE widgets.type='specific_type'
etc...

Remove the quotes. And add columns to select, e.g. *.

SELECT * FROM widgets
INNER JOIN widget_login ON widget_login.id=widgets.id
INNER JOIN widget_admin ON widget_admin.id=widgets.id
INNER JOIN widget_facebook ON widget_facebook.id=widgets.id
WHERE ... etc etc

EDIT: If you only want to select from widgets, then use EXISTS:

SELECT * FROM widgets
WHERE EXISTS (select * from widget_login where widget_login.id=widgets.id)
AND EXISTS (select * from widget_admin ON widget_admin.id=widgets.id)
AND EXISTS (select * from widget_facebook ON widget_facebook.id=widgets.id)
WHERE ... etc etc

or IN:

SELECT * FROM widgets
WHERE id IN (select id from widget_login)
AND id IN (select id from widget_admin)
AND id IN (select id from widget_facebook)
WHERE ... etc etc

BTW: It seems strange that the IDs of the various tables shall equal. A column named ID in a table widgets should identify a widget record, wheras a column named ID in a table widget_login should identify a login record. Check this; maybe you are confusing IDs.

EDIT: One more remark: If you are looking for widgets that exist in at least one of the three tables use EXISTS or IN with OR instead of AND.

EDIT: To join all tables and make sure that the widget exists in at least one of the tables use outer join and check your results for nulls:

SELECT * FROM widgets
LEFT JOIN widget_login ON widget_login.id=widgets.id
LEFT JOIN widget_admin ON widget_admin.id=widgets.id
LEFT JOIN widget_facebook ON widget_facebook.id=widgets.id
WHERE (widget_login.id IS NOT NULL OR widget_admin.id IS NOT NULL OR widget_facebook.id IS NOT NULL)
AND ... etc etc

Be aware with further criteria that the columns of the other tables can be null for a record. E.g. to add AND widget_admin.name <> 'BAD' would remove all outer joined records, because NULL is not <> 'BAD'.

Replace JOIN with LEFT JOIN. It get all rows from widgets and related rows (if exist) from joined tables

SELECT FROM widgets
LEFT JOIN widget_login ON widget_login.id=widgets.id
LEFT JOIN widget_admin ON widget_admin.id=widgets.id
LEFT JOIN widget_facebook ON widget_facebook.id=widgets.id
WHERE ... etc etc

This is effectively equal to the INNER JOIN . But will show records only if the id exist in all your child tables. You can modify the WHERE condition to filter out the rows

  SELECT FROM widgets
    LEFT OUTER JOIN widget_login ON widget_login.id=widgets.id
    LEFT OUTER JOIN widget_admin ON widget_admin.id=widgets.id
    LEFT OUTER JOIN widget_facebook ON widget_facebook.id=widgets.id
    WHERE widget_login.id IS NOT NULL 
          AND  widget_admin.id IS NOT NULL 
          AND  widget_facebook.id IS NOT NULL