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?
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.
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!