CODEWITHSUNDEEP
mysql
Linkandzelda
Linkandzelda

Reputation: 611

MySQL Select 2 items from multiple tables

I'm trying to select 2 of the same item from multiple tables. Here's a run down of my data.

Items

Item ID    Item name
1          Item1
2          Item2
3          Item3
4          Item4

Item Pairs

Item ID 1  Item ID 2
4          1
4          3
3          1
4          2

The pairs are linked via foreign key.

What I need to be able to do is select the 2 item names based on the item pair ids. I got as far as selecting only 1 of them with the below query but have not been able to select more than that.

SELECT item_name FROM items, item_pairs WHERE item_pairs.item_id_1 = item.item_id

Thanks very much in advance

Upvotes: 1

Views: 388

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

Try this one by rejoining the items table

SELECT 
  i.item_name,
  i2.item_name 
FROM
  items i 
  JOIN item_pairs ip 
    ON ip.item_id_1 = i.item_id 
  JOIN items i2 
    ON ip.item_id_2 = i2.item_id 
GROUP BY ip.item_id_1,
  ip.item_id_2

Upvotes: 2

Related Questions