How to combine internal and external join in mysql

Question

I can do it in sybase, and I can do it in oracle, but I don't see how to do it in mysql.

I have this: (please restrain yourself from reformatting my sql, the last time someone did that they changed it, so it was not the same and made the question pointless)

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 kinds of results that do not make sense.

I want to get a list of all identifiers from table1, where table2.data = cat, then do an external join with the results of this in table 3, where table3.data = dog.

I notice that I cannot specify the same table / alias for table1 in two join clauses, so I should assume that mysql runs the join expressions separately and ORing the results together or something like that.

I also tried to get rid of the “inner join” in the from section and putting it in the where clause, which didn't work either, although it didn't work differently (got different erroneous results)

It would be so simple in sea bass 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 will never want to use a comma in a from statement. I believe the comma is equivalent to saying cross-connect. Not sure though, but I think this query is what you are looking for.