| Another JOIN Thread
|
wiglaf
msg:4078678 | 10:04 pm on Feb 11, 2010 (gmt 0) |
Two tables:
table: companies
| company | info |
==================
| company x | info a |
| company y | info b |
| company z | info c |
table: items
| item | company |
==================
| item 1 | company x |
| item 2 | company z |
| item 3 | company z |
What I want is a result with all the items appearing once with all the company info, but I need companies with no items to occupy a row too. The problem I'm having is that companies that have no items will not show up. So in my example company y will not show up. I've tried all manner of JOINs, but they all seem to have the same behavior. I want something like this for my result, but don't know if it's possible:
| company | info | item |
=========================
| company x | info a | item 1 |
| company y | info b | NULL |
| company z | info c | item 2 |
| company z | info c | item 3 |
|
LifeinAsia
msg:4078680 | 10:10 pm on Feb 11, 2010 (gmt 0) |
| I've tried all manner of JOINs |
|
Did you try a LEFT OUTER JOIN?
SELECT companies.company, companies.info, items.item
FROM companies LEFT OUTER JOIN items ON companies.company=items.company
|
wiglaf
msg:4078708 | 10:51 pm on Feb 11, 2010 (gmt 0) |
I tried that and got the same result, but it seems that the example I gave is too simple for whats going on with my query (what you suggested does in fact work on my example).
There is actually a third table at play here, which I thought was not needed in the example. I am electing not to continue the thread past this point, because I'm in the process of re-writing the schema of relations to that third table.
Thanks for the response, and again, your suggestion does work on the example I gave.
|
|
|
