0

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')
      )
 );
Improve this question
2
  • Don't mix implicit and explicit join syntax. Always use join for all tables. And where comes after join Commented Mar 30, 2016 at 16:26
  • I've edited SQL stmt but it returns suppliers of chosen item, not suprisingly ... Commented Mar 30, 2016 at 17:21

2 Answers 2

Reset to default
1

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

18 Comments

It works but there's big BUT. If item = Name1 has more suppliers' pairs - it returns other item names for every supplier pair value e.g: Name1 and other items with suppliers(PK_ID=1) then again Name1 and other items but with suppliers(PK_ID=4)
If Name1 has multiple suppliers, the query will return all items from each supplier. That's what your question asked for.
but it returns it in looped fashion: 1. Take (Name1 and its FIRST supplier) -> 2. Show other NameX with taken supplier and show only ONE supplier -> 3. Take (Name2 and its now SECOND supplier) etc...
What I'd like to have: take ITEM1 and its all suppliers and find next item with every matched supplier ONLY THEN find next item with every supplier that matches.
So you want to see all suppliers for Item1, then all suppliers for Item2 etc.? If that's correct I can do it, but it will be a bit ugly.
|
0

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.

Improve this answer

9 Comments

It works BUT then again: if I select supplier and supplier_code to first row of your stmt it returns results in loop - it's taking into account one supplier and is looking for other items with this supplier and once again next supplier and once again is looking for other items with this supplier... I believe there's simpler solution, maybe with JOIN where all results are shown at once - but thanks for working reply and step-by-step instruction, it helps
What I'd like to have: take ITEM1 and its all suppliers and find next item with every matched supplier ONLY THEN find next item with every supplier that matches.
You make this sound like a recursion, but it's simply a step-by-step approach: Get the item, get its suppliers, get their items. How else would you address this problem? The query is straight-forward and should result in a good execution plan. You can do the same with some joins of course, which is not as straight-forward, however. EDIT: This is the answer to your first comment. I am reading the second right now ...
As to your second comment: It seems you are trying to find the best algorithm to access the table records. But this is not your job and it is not how SQL works. In SQL you only tell the DBMS what to select (and you use the most simple and readable query for this), then let the DBMS find the best access method. It is made for this job, so trust it to do it well.
I feel like it can be simplified with JOINs :) Close to be perfect.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.