New Posts  All Forums:Forum Nav:

mysql, outer joins

post #1 of 2
Thread Starter 
Is anyone on here any good at MySQL outer joins?

Essentially I want to left join a record to another left joined record but keep getting the syntax wrong. (is it just me, or is MySQL's syntax completely broken compared to other RDBMS? I swear I never had this problem with Oracle)


anyway, I have 3 tables:

table1:
Code:
 ____________________________________
|                 |                  |
|        id       |        table2_id |
|_________________|__________________|
|                 |                  |
| 1               | 4                |
|_________________|__________________|
|                 |                  |
| 2               | null             |
|_________________|__________________|
|                 |                  |
| 3               | 7                |
|_________________|__________________|

table2:
Code:
 ____________________________________
|                 |                  |
|        id       |        table3_id |
|_________________|__________________|
|                 |                  |
| 4               | 4                |
|_________________|__________________|
|                 |                  |
| 7               | null             |
|_________________|__________________|

table3:
Code:
 _________________
|                 |
|        id       |
|_________________|
|                 |
| 6               |
|_________________|
and need the data to come out like this:
Code:
 _____________________________________________________________________________________________
|                 |                  |                  |                  |                  |
| table1.id       | table1.table2_id | table2.id        | table2.table3_id | table3.id        |
|_________________|__________________|__________________|__________________|__________________|
|                 |                  |                  |                  |                  |
| 1               | 4                | 4                | 6                | 6                |
|_________________|__________________|__________________|__________________|__________________|
|                 |                  |                  |                  |                  |
| 2               | null             | null             | null             | null             |
|_________________|__________________|__________________|__________________|__________________|
|                 |                  |                  |                  |                  |
| 3               | 7                | 7                | null             | null             |
|_________________|__________________|__________________|__________________|__________________|


This was so simple in Oracle, you'd just add (+) to the field you want outer joined when doing a normal comparison in the WHERE part of the code. But MySQL needs the comparison made in the FROM part - which seems completely backwards and I still can't seem to get nested joins working mad.gif


NB, I know this could be done as nested sub-queries, but I'd rather avoid that if I can as the above method should be more efficient.
Edited by Plan9 - 11/2/12 at 9:03am
post #2 of 2
Thread Starter 
Cracked it. Seems I was mixing up ANSI-89 with ANSI-92 style joins which was altering the precedence of joins. So it was user error not knowing ANSI SQL properly rather than any issue with MySQL itself redface.gif

In case anyone is interested, below is the source that lead me to my solution:
http://stackoverflow.com/questions/4065985/mysql-unknown-column-in-on-clause

( I'd be utterly lost without Stack Exchange laugher.gif )
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Coding and Programming