Jump to content

Joining Tables (SQL)


Hare
 Share

Recommended Posts

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.

Screen%20Shot%202017-06-25%20at%207.13.0

Edited by Hare
Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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 by Hare
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Hare
Link to comment
Share on other sites

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 by Anoua
  • Thanks 1
Link to comment
Share on other sites

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

 

  • Like 1
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...