Joining Multiple Tables

luiddog

I have 4 tables that I want to all LEFT JOIN. So I want all the records from table1 to be displayed then any corresponding entries in table2, table3, table4. There are multiple entries that correspond to table1.

SELECT * FROM table1 LEFT JOIN table2 ON table1.ID=table2.ID LEFT JOIN table3 ON table1.ID=table3.ID LEFT JOIN table4 ON table1.ID=table4.ID

What am I doing wrong?

I want all of table1 then any and all corresponding to table1 from tables 2,3,4

jheitz

Your select seems fine, what is the issue?

luiddog

I've actually worked around this by just making multiple DB connections to each table based on the ID. But here is some more info on my challenge

I think it does work but I'm having problems displaying the results.

Here are some example tables:

table1

| ID | Name |

|-----------------|

| 1 | Tom |

| 2 | Dick |

| 3 | Harry |

table2

| FID | ID | Food |

|----------------------|

| 1 | 1 | Cake |

| 2 | 1 | Coffee |

| 3 | 2 | Pizza |

| 4 | 3 | Beer |

table3

| SID | ID | Side |

|----------------------|

| 1 | 1 | Fries |

| 2 | 1 | O Rings |

| 3 | 2 | Salad |

| 4 | 2 | Rice |

table4

| PID | ID | Pay |
|----------------------|
| 1 | 1 | Cash |
| 2 | 2 | Credit |

| 3 | 3 | Cash |

What I want is something like this for each person's page

Tom - Coffee, Cake - Fries, O Rings - Cash
Dick - Pizza - Salad, Rice - Credit
Harry - Beer - Cash

But what I get is either just
Tom - Coffee - Fries - Cash

or if I use Repeat Regions (DW sorry)

Tom - Coffee - Fries - Cash
Tom - Cake - O Rings - Cash

jheitz

In your example, a left outer join for John on all tables should return:

John - Cake - Fries - Cash
John - Coffee - Fries - Cash
John - Cake - O Rings- Cash
John - Coffee - O Rings- Cash

Which is every single combination of the things John likes according to your data. With this data you could then easily produce an array to reflect what you want and then display it:

Tom - Coffee, Cake - Fries, O Rings - Cash