Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

Another JOIN Thread



10:04 pm on Feb 11, 2010 (gmt 0)

5+ Year Member

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 |


10:10 pm on Feb 11, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month

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


10:51 pm on Feb 11, 2010 (gmt 0)

5+ Year Member

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.

Featured Threads

Hot Threads This Week

Hot Threads This Month