CODEWITHSUNDEEP
mysqlsql
Some Guy
Some Guy

Reputation: 13568

How to select from a table and up to two other items

In my database I have three tables (don't ask why it was set up this way, it just was, this is a simplification but gets at the problem):

Table: players
id
username
weapon_id
shield_id

Table: items
id
name
stats (either attack or defend stats number)
item_type_id

Table: item_types
id
is_weapon (true if weapon, false if shield)
is_rare (true/false boolean)

Each player has either no items, 1 item, or 2 items. (Either a weapon, a shield, both, or neither, in which case the weapon_id and shield_id would be null)

I want to run a select query that gets:

Results of my query

id (of player)
username
weapon_id
weapon_name
weapon_stats
weapon_is_rare
shield_id
shield_name
shield_stats
shield_is_rare

In the case where shield_id is null, for example, the values in the result should just be null.

What's a good SQL query to get this sort of result from this setup?

Upvotes: 0

Views: 32

Answers (3)

Barmar
Barmar

Reputation: 781741

You need to do a LEFT JOIN with the items and item_types table separately for the weapon and shield. Use table aliases to distinguish which join you're referencing.

SELECT 
  p.id
 ,p.username
 ,p.weapon_id
 ,w.name     AS weapon_name
 ,w.stats    AS weapon_stats
 ,wt.is_rare  AS weapon_is_rare
 ,p.shield_id
 ,s.name     AS shield_name
 ,s.stats    AS shield_stats
 ,st.is_rare  AS shield_is_rare
FROM players AS p
LEFT JOIN items AS w ON w.id = p.weapon_id
LEFT JOIN item_types AS wt ON wt.id = w.item_type_id
LEFT JOIN items AS s ON s.id = p.shield_id
LEFT JOIN item_types AS st ON st.id = s.item_type_id

Upvotes: 0

McGlothlin
McGlothlin

Reputation: 2099

Start with the players table as the main table you are querying. As others have said, you are going to want to LEFT JOIN the other tables to the players table on the foreign keys that match.

If you aren't sure which join type to use, look here: http://www.techonthenet.com/oracle/joins.php

That page is for Oracle DB, but the same concept applies to all flavors of SQL.

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 175944

SELECT 
  p.id
 ,p.username
 ,p.weapon_id
 ,i1.name     AS weapon_name
 ,i1.stats    AS weapon_stats
 ,i1.is_rare  AS weapon_is_rare
 ,p.shield_id
 ,i2.name     AS shield_name
 ,i2.stats    AS shield_stats
 ,i2.is_rare  AS shield_is_rare
FROM players p
LEFT JOIN (SELECT i.id, i.name, i.stats, it.is_rare
           FROM items i 
           JOIN item_types it 
             ON i.item_type_id = it.id) AS i1
  ON p.weapon_id = i1.id
LEFT JOIN (SELECT i.id, i.name, i.stats, it.is_rare
           FROM items i 
           JOIN item_types it 
             ON i.item_type_id = it.id) AS i2
  ON p.shield_id = i2.id;

Upvotes: 0

Related Questions