mysql - joining three tables with HAVING

Question

I have table:

id name type

where "type" is 1 or 2

I need to join this table with two other. Rows with "type = 1" should be joined with first table, and =2 with second.

For example, main table contains some events that peoples did. Fist table to join is information about mans, and second is about women. Type is sex.
I need take last 10 events with all information about these peoples.

Something like

SELECT *
FROM tbl
INNER JOIN tbl_1 ON tbl.name = tbl_1.name HAVING tbl.type = 1
INNER JOIN tbl_2 ON tbl.name = tbl_2.name HAVING tbl.type = 2

But it does not working.
How it can be implemented?

Answer 1

It looks like you're trying to tie two separate data sets together - have you considered using a Union All on two queries? Something like:

select * from tbl inner join tbl_1 on tbl.name = tbl1.name where tbl.type = 1
union all
select * from tbl inner join tbl_2 on tbl.name = tbl2.name where tbl.type = 1

This, of course, assumes your two joined tables have the same column configuration.

The use of Having doesn't make sense in this case as you are not performing an aggregate query.

Answer 2

Firstly, the HAVING clause is for grouping, not joins. Simply include the condition in the ON clause

ON tbl.name = tbl_1.name AND tbl.type = 1

Secondly, if the condition is unfulfilled, the row won't appear in the result of an inner join. Since a field (tbl.type) can't have two different values, no rows will result. Try a left outer join instead.

SELECT *
  FROM tbl
  LEFT JOIN tbl_1 ON tbl.name = tbl_1.name AND tbl.type = 1
  LEFT JOIN tbl_2 ON tbl.name = tbl_2.name AND tbl.type = 2

However, this brings up the question: why cant you allow all fields in the result, and ignore the ones you don't care about?