homepage Welcome to WebmasterWorld Guest from 54.234.141.47
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Ignore empty fields on join
Ignore empty fields on join
Knowles

10+ Year Member



 
Msg#: 3709352 posted 4:14 pm on Jul 28, 2008 (gmt 0)

I have a join call from 3 different tables, the problem is sometimes the field for one table will be empty which will make one of the tables completely unneeded. Since this happens it is calling my database call to fail and return no results.

SELECT villages.name as vname, players.name as pname, tribes.name as tname, tribes.id as tid, players.id as pid, villages.id as vid FROM villages, players, tribes WHERE villages.x = '500' AND villages.y = '500' AND players.ally = tribes.id AND villages.player = players.id"

What happens is sometimes there no "players.ally" to send to "tribes.id" which makes the "tribes.name" and "tribes.id" not needed. Instead of those fields coming back as blank they are completely killing the select.

I have tried:
SELECT villages.name as vname, players.name as pname, IFNULL(tribes.name, '')as tname, IFNULL(tribes.id, '') as tid, players.id as pid, villages.id as vid FROM villages, players, tribes WHERE villages.x = '500' AND villages.y = '500' AND players.ally = tribes.id AND villages.player = players.id

But the result still comes up empty. Anyone have any suggestions?

 

JamieBrown

5+ Year Member



 
Msg#: 3709352 posted 4:34 pm on Jul 28, 2008 (gmt 0)

I think you might be looking for a LEFT JOIN. When you list your tables like that you're essentially doing an INNER JOIN. If you write out the syntax for a LEFT JOIN manually it should work.

Something like:

FROM players INNER JOIN villages ON villages.player = players.id LEFT JOIN tribes ON tribes.id = players.ally

Watch out for LEFT JOINs on big tables though - they can be slow and resource intensive. If its data you need to use really regularly you can break some normalisation rules every now and again, as long as you do it carefully!

Jamie.

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 3709352 posted 4:39 pm on Jul 28, 2008 (gmt 0)

One step further:
FROM players INNER JOIN villages ON villages.player = players.id LEFT JOIN tribes ON tribes.id = players.ally
WHERE tribes.name IS NOT NULL

Knowles

10+ Year Member



 
Msg#: 3709352 posted 5:00 pm on Jul 28, 2008 (gmt 0)

Thanks for the help guys, I got it figured out with the left joins... I knew that answer two :( darnit!

JamieBrown

5+ Year Member



 
Msg#: 3709352 posted 5:04 pm on Jul 28, 2008 (gmt 0)

Great! Glad to help.

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