Joining Tables (SQL)

[Hare]

Hello! I have an SQL question about joining 3 tables for my game?

I have rabbits and guinnea pigs in my SIM. When you look in your shed, you see all your cages. The cages can either be empty, contain a rabbit, or contain a guinnea pig. I got this to work (shown in screenshot), but it doesn't allow you to ORDER BY pig and rabbit data.

I can get ORDER BY working if I join the Cages_Table with either Rabbit_Table or Pig_Table table, but not both.

The rabbit and pig tables don't have a common ID with each other, they only link to the Cage_Table.  The Cage_Table has a column for rabbit ID and a column for pig ID. 0 means none. If both are 0, the cage is empty (one of them will always be 0).

Is possible to join the Cages_Table to both Rabbit_Table AND Pig_Table or do I need to go about this another way?

Any help would be much appreciated. I've been messing with this thing all day without much luck and am pretty baffled. Not sure if what I'm trying to do is even possible.

This is the code that works but only joins rabbits to cages:

SELECT CAGE_TABLE.* FROM CAGE_TABLE
LEFT JOIN RABBIT_TABLE ON (CAGE_TABLE.rabbitid=RABBIT_TABLE.rabbitid) 
WHERE CAGE_TABLE.shed='$shedid' ORDER BY $sort1 ASC, $sort2 ASC

And this is one of the codes I tried that doesn't work (I've been trying a lot of other stuff though):

SELECT CAGE_TABLE.* FROM CAGE_TABLE
LEFT JOIN RABBIT_TABLE ON (CAGE_TABLE.rabbitid=RABBIT_TABLE.rabbitid) 
LEFT JOIN PIG_TABLE ON (CAGE_TABLE.pigid=PIG_TABLE.pigid) 
WHERE CAGE_TABLE.shed='$shedid' ORDER BY $sort1 ASC, $sort2 ASC

 

Screenshot of the working code in action. If only I could sort the pigs and rabbits by breed and other data, and get the empty cages to show up last, it would be perfect. 

Please excuse the -1 years and months old, that'll be fixed later.

Edited June 25, 2017 by Hare

[Digital]

Looking at this on mobile, but it what values are you using in sort1 or sort2? Are you remembering to use the table prefixes for those sorts? If you wanted to sort on a field in RABBIT_TABLE, the sort should be RABBIT_TABLE.field instead of field.

Likewise on the main table, CAGE_TABLE. 

[Hare]

48 minutes ago, Digital said:

Looking at this on mobile, but it what values are you using in sort1 or sort2? Are you remembering to use the table prefixes for those sorts? If you wanted to sort on a field in RABBIT_TABLE, the sort should be RABBIT_TABLE.field instead of field.

Likewise on the main table, CAGE_TABLE. 

Thanks Digital! Yes I included the table. (like RABBIT_TABLE.breed). It works with the rabbit table and cage table, but I can't get it to sort with pig table. Am I right in assuming my query needs to join all three tables in order to sort by all three? 

Edited June 25, 2017 by Hare

[juliet]

Yes, you can join multiple tables. Typically you would include fields from those tables in your select and then use those fields in your order by. (If I am understanding what you are trying to do)

Selecting * is not recommended.

[Hare]

22 minutes ago, juliet said:

Yes, you can join multiple tables. Typically you would include fields from those tables in your select and then use those fields in your order by. (If I am understanding what you are trying to do)

Selecting * is not recommended.

Thank you =D I've done it without the *, just having the columns instead (ike CAGE_TABLE.rabbit, RABBIT_TABLE.breed, PIG_TABLE.breed. I can't get it to give me any results either way when I join 3 tables. It only works when I join 2 tables. 

Edited June 25, 2017 by Hare

[juliet]

Is it giving you any kind of error? Because if you have fields with the same name in multiple tables, and select * from those tables there may be a conflict.

[Anoua]

If you explain what you have in $sort1 and $sort2 that would help figure out where you are going wrong. Without this data, it's hard to know exactly what you are trying to sort by. 

But one fucntion that may be useful to you is coalesce. This function takes in values and will return the first one that isn't null. What this allows is then is say you have rabbit_table.name and pig_table.name, you can do COALESCE(RABBIT_TABLE.name, PIG_TABLE.name) as animal_name to return the animal name If you put this in the SELECT section as a column option this should also then allow you to sort by the animal_name value regardless of whether they are a pig or a rabbit. And the same will work for other columns too. The main reason it works is because the left join will return null values for the rabbit table values for a pig, and null values for the pig table values for a rabbit. 

Hope that helps and let me know if i need to clarify anything.

 

Edited June 25, 2017 by Anoua

[Hare]

I got it working! Thank you everyone for the tips. I'll get the query all set up and try coalescue, then post it  here for anyone who might need it for reference. 

[Hare]

So here is the working code.

Here are the sorting variables (it gets the custom sort options that a player has in their settings). 

$sort1 = 'breed';
$sort2 = 'name';

SELECT CAGE_TABLE.rabbit, CAGE_TABLE.pig, CAGE_TABLE.id, 
PIG_TABLE.breed, PIG_TABLE.name,
RABBIT_TABLE.breed, RABBIT_TABLE.name,
COALESCE (RABBIT_TABLE.breed, PIG_TABLE.breed) as animal_breed, 
COALESCE (RABBIT_TABLE.name, PIG_TABLE.name) as animal_name
FROM CAGE_TABLE
LEFT JOIN RABBIT_TABLE ON CAGE_TABLE.rabbit=RABBIT_TABLE.id
LEFT JOIN PIG_TABLE ON CAGE_TABLE.pig=PIG_TABLE.id
WHERE CAGE_TABLE.building='$player_building_id' 
ORDER BY CASE WHEN RABBIT_TABLE.id IS NULL THEN PIG_TABLE.id IS NULL ELSE 0 END,  
animal_$sort1, animal_$sort2

And then we have the SQL statement above. It gets the name and breed columns for each animal table and uses coalesce to turn them into animal_breed and animal_name so we can easily put the variable in for whatever data we want to order by.

I added all the other sort options too (not shown in this example). This is the simplified version.

I also added a CASE BY to make empty cages show up last. I don't fully understanding what the else 0 means but it's doing the job. =D