Welcome to WebmasterWorld Guest from 34.204.176.189

Forum Moderators: open

Message Too Old, No Replies

Inner Join Woes

I want a set even where Inner Join doesn't match

     
2:40 pm on Sep 24, 2006 (gmt 0)

Senior Member

WebmasterWorld Senior Member trillianjedi is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Apr 15, 2003
posts:7256
votes: 3


Hi guys.

I'm using Inner Join to pull data out of a second table, but I want a row even where the "ON" is not matched, eg the query looks something like:-

SELECT name, age, table2.job FROM table1
INNER JOIN
table2 ON name = surname
ORDER BY DateTime DESC
LIMIT 5;

Basically I want a row returned even where name <> surname and in that case I want name to be displayed as "NULL".

Any thoughts?

Thanks,

TJ

8:13 am on Sept 25, 2006 (gmt 0)

New User

10+ Year Member

joined:Feb 2, 2005
posts:34
votes: 0


Hi,

I think you have to use LEFT JOIN instead og the INNER JOIN. That way all rows from the left table will be returned with the NULL values of the column that did not match the column from the right table.

9:04 am on Sept 25, 2006 (gmt 0)

Senior Member

WebmasterWorld Senior Member trillianjedi is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Apr 15, 2003
posts:7256
votes: 3


Thank you Rebrant, I'll give it a go!

TJ

1:58 pm on Sept 25, 2006 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:June 13, 2002
posts:2175
votes: 0


Yes that should fix it. On any join columns where you allow null values, you do have to remember to use outer joins instead of inner joins.

The other way is to have a dummy record in the master table and set a default value in the child table to point to the dummy. That keeps the db using all inner joins but might not be appropriate for your appp.

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members