How do you combine an inner and outer join in mysql

Question

I can do it in sybase and I can do it in oracle, but I'm not seeing how to do it in mysql.

I've got this: (please restrain yourself from re-formatting my sql, last time somebody did that they changed it so it wasn't the same and made the question meaningless)

select table1.id
from
table1
  inner join
    table2 on (table1.id = table2.id and table2.data='cat'),
table1 t1
  left outer join
    table3 on (t1.id = table3.id and table3.data = 'dog')

And I get all sorts of results that make no sense.

I want to get a list of all of the id's from table1 where table2.data = cat, then do an outer join with the results of that against table 3 where table3.data = dog.

I notice that I can't specify the same table/alias for table1 in the two join clauses, so that leads me to believe that mysql is running the join expressions separately and ORing the results together or something like that.

I also tried getting rid of the "inner join" in the from section and putting it in the where clause, that didn't work either, though it didn't work in a different way (got different erroneous results)

This would be so easy in sybase or oracle.

What am I doing wrong?

Answer 1

select table1.id
from
table1
  inner join
    table2 on (table1.id = table2.id and table2.data='cat')
  left outer join
    table3 on (table1.id = table3.id and table3.data = 'dog')

I think you never want to use a comma in the from statement. I believe the comma is equivalent to saying cross join. Not sure though, but I think this query is what you're looking for.