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)

New User

5+ Year Member

joined:Jan 9, 2008
posts: 19
votes: 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 |
10:10 pm on Feb 11, 2010 (gmt 0)

Moderator from US 

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

joined:Dec 10, 2005
votes: 24

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)

New User

5+ Year Member

joined:Jan 9, 2008
votes: 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.