Welcome to WebmasterWorld Guest from 54.221.87.97

Forum Moderators: open

Message Too Old, No Replies

Ignore empty fields on join

Ignore empty fields on join

     

Knowles

4:14 pm on Jul 28, 2008 (gmt 0)

10+ Year Member



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

4:34 pm on Jul 28, 2008 (gmt 0)

10+ Year Member



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

4:39 pm on Jul 28, 2008 (gmt 0)

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



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

5:00 pm on Jul 28, 2008 (gmt 0)

10+ Year Member



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

JamieBrown

5:04 pm on Jul 28, 2008 (gmt 0)

10+ Year Member



Great! Glad to help.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month