homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Another JOIN Thread

5+ Year Member

Msg#: 4078676 posted 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 |



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

Msg#: 4078676 posted 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


5+ Year Member

Msg#: 4078676 posted 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.

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved