CODEWITHSUNDEEP
mysqlsqljoinforeign-keysrelational-database
SQLnoob
SQLnoob

Reputation: 13

SQL selecting rows using FOREIGN KEYS

suppliers table:

PK_ID  supplier  supplier_code
  1       x           abc
  2       y           def

items table:

PK_ID   item_name   
 1        Name1           
 2        Name2

items_suppliers table with FOREIGN KEYS:

FK_ID_items  FK_ID_suppliers           
    1              1
    2              1

I'd like to:

Select ONE item and find other items with the same suppliers from suppliers table. It should return "Name2" item then.

[edited]:

Take ITEM1 and its all suppliers and find next item with every matched supplier ONLY THEN find next item with every supplier that matches.

PROPER ANSWER (CREDIT TO @DarwinvonCorax - with @ThorstenKettner invaluable help):

SELECT i2.item_name, s.supplier, s.supplier_code
FROM items i1
JOIN items_suppliers is1
ON i1.PK_ID = is1.FK_ID_items
JOIN items_suppliers is2
ON is1.FK_ID_suppliers = is2.FK_ID_suppliers
JOIN items i2
ON is2.FK_ID_items = i2.PK_ID
JOIN suppliers s
ON is1.FK_ID_suppliers = s.PK_ID
WHERE i1.item_name = 'Name1'
ORDER BY
CASE WHEN i2.item_name = 'Name1'
     THEN 1
     ELSE 2
  END,
i2.item_name, s.supplier;

The solution proposed by @ThorstenKettner is also correct:

select item_name
 from items
where pk_id in
(
 select fk_id_items
   from items_suppliers
  where fk_id_suppliers in
  (
    select fk_id_suppliers  
    from items_suppliers
    where fk_id_items = 
      (
       select pk_id from items where item_name = 'Name1')
      )
 );

Upvotes: 0

Views: 65

Answers (2)

Darwin von Corax
Darwin von Corax

Reputation: 5246

You need to do a palindromic join (my term.) First find the supplier ID for the specified item, then from items_suppliers find all records for the same supplier ID. You join items to items_suppliers to items_suppliers to items. Then tack on a join to suppliers at the end to get the supplier name.

Getting the forced sort order you want is a little trickier. I've used a hack I've seen elsewhere, but I honestly don't know how common it is. I do a SELECT to get just the suppliers for Item1, and add a constant column of value 1 to the result. I do a second SELECT to get the suppliers for all items except Item1, and add constant column 2. UNION these two queries and from that result select just the columns you want, but ORDER BY the constant, which will sort all rows with value 1 (the suppliers for Item1) first.

SELECT item_name, supplier, supplier_code
  FROM
    (SELECT '1' AS orderer, i1.item_name AS item_name, s.supplier AS supplier, s.supplier_code AS supplier_code
      FROM items i1
      JOIN items_suppliers is1
        ON i1.PK_ID = is1.FK_ID_items
      JOIN suppliers s
        ON is1.FK_ID_suppliers = s.PK_ID
      WHERE i1.item_name = 'Name1'
    UNION ALL
    SELECT '2', i2.item_name, s.supplier, s.supplier_code
      FROM items i1
      JOIN items_suppliers is1
        ON i1.PK_ID = is1.FK_ID_items
      JOIN items_suppliers is2
        ON is1.FK_ID_suppliers = is2.FK_ID_suppliers
      JOIN items i2
        ON is2.FK_ID_items = i2.PK_ID
      JOIN suppliers s
        ON is1.FK_ID_suppliers = s.PK_ID
      WHERE i1.item_name = 'Name1'
        AND i2.PK_ID <> i1.PK_ID
    ) r
  ORDER BY orderer, item_name, supplier;

Based on Thorsten Kettner's comments, though, I realize my SQL is just a little rusty and out-of-date; his suggestion would look something like this:

SELECT i2.item_name, s.supplier, s.supplier_code
  FROM items i1
  JOIN items_suppliers is1
    ON i1.PK_ID = is1.FK_ID_items
  JOIN items_suppliers is2
    ON is1.FK_ID_suppliers = is2.FK_ID_suppliers
  JOIN items i2
    ON is2.FK_ID_items = i2.PK_ID
  JOIN suppliers s
    ON is1.FK_ID_suppliers = s.PK_ID
  WHERE i1.item_name = 'Name1'
  ORDER BY
    CASE WHEN i2.item_name = 'Name1'
         THEN 1
         ELSE 2
      END,
    i2.item_name, s.supplier;

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

Do that step by step:

  1. Find the item ID for item 'Name1'.
  2. Find all suppliers for that item ID.
  3. Find all item IDs of these suppliers.
  4. Find the item names for the found IDs.

The query:

select item_name
from items
where pk_id in
(
  select fk_id_items
  from items_suppliers
  where fk_id_suppliers in
  (
    select fk_id_suppliers  
    from items_suppliers
    where fk_id_items = (select pk_id from items where item_name = 'Name1')
  )
);

You can add and item_name <> 'Name1' if you want to exclude that from your list.

Upvotes: 0

Related Questions