I have three tables. One of them contains sales order info. It looks something like this:

OrderNo (PK) ----- OrderDetail (PK) ----- ItemID (FK)
---1 ----------------- 1 --------------------2
---1 ------------------2 --------------------3
---2 ------------------1 --------------------3
---3 ------------------1 --------------------1
---3 ------------------2 --------------------5

Another table contains Item information:

ItemID (PK) ----- Description ----------- ItemType
---1 ------------------PC----------------Equipment
---2 ---------------Printer ---------------Equipment
---3 ------------Black Ink Cartridge-------Accessory
---4 ---------------Memory--------------Accessory
---5 -------------Graphics Card ----------Accessory
---6 -------------Mac Computer----------Equipment

And a third table indicates which items are related to which other items. The RelatedItem field is an ItemID:

ItemID(PK) ----- RelatedItem(FK)
---1 ----------------- 1
---1 ----------------- 2
---1 ----------------- 4
---1 ----------------- 5
---2 ----------------- 1
---2 ------------------2
---2 ----------------- 5
---3 ------------------2
---4 ------------------1
---5 ------------------1
---5 ------------------6
---6 ------------------6
---6 ------------------5

It basically indicates which equipment an accessory is associated with. If the item is a piece of equipment, then it is associated with itself, but it can also be associated with another piece of equipment.

I'm trying to create a query that will join these three tables, but I only want the RelatedItem to show if the RelatedItem is part of my sales order. Here is what I would want my query result set to look like for the third sales order:

OrderNo ---- OrderDetail ---- ItemID ---- RelatedItem
--3 ------------1 -------------1------------1
--3 ------------2 -------------5------------1

As opposed to this:

OrderNo ---- OrderDetail ---- ItemID ---- RelatedItem
--3 ------------1 -------------1------------1
--3 ------------1 -------------1------------2
--3 ------------1 -------------1------------4
--3 ------------1 -------------1------------5
--3 ------------2 -------------5------------1
--3 ------------2 -------------5------------6

Basically, the equipment item needs to have the RelatedItem point back to its own itemID, while each accessory item points back to the ItemID of the equipment it's associated with. I don't want to display the other records.

Obviously, I can join the order table and the item table based on the ItemID. But how can I join to the RelatedItems table while only bringing in the RelatedItems that I need for that sales order?

I would greatly appreciate any ideas on how to go about this.

Thank you,

Hammerklavier

Try this:

Wim,

Thank you for responding.

Part of my problem is that I need the result set to have the same number of records as my orders table. Items that don't have a relateditem on the sales order should be part of the result set with a blank relateditem field rather than being left off of it.

Using left joins, on the other hand, seems to give me too many records.

An update that might help to explain what's going wrong.

In the RelatedItems table, the ItemID is always an Equipment and the RelatedItem is always a Accessory. Equipments never are related to themselves.

Unfortunately, I'm as confused as ever on how to approach this.

Allow me to clarify...

Sample data:

OrderDetails
SOID---DetailID---ItemID
1--------1--------254
1--------2--------260
1--------3--------216
1--------4--------285

Items
ItemID---ItemType
254------Equipment
257------Equipment
260------Equipment
216------Accessory
285------Accessory
287------Accessory

RelatedItems
ItemID--RelatedItemID
254--------216
254--------285
254--------287
257--------216
260--------287

What I want to see in my result set
SOID---DetailID---ItemID--ItemType---RelatedItem
1--------1--------254-----Equipment---254
1--------2--------260-----Equipment---260
1--------3--------216-----Accessory---254
1--------4--------285-----Accessory---254

So basically, if the ItemType is "Equipment", I want the RelatedItem to be the ItemID for that Item. If the ItemType is "Accessory", I want the RelatedItem to be the Equipment Item it relates to on that sales order.

Here is the SQL query I'm trying to use:


select orderdetails.SOID, orderdetails.detailid, items.itemid, Items.ItemType,
Equipment =
CASE Items.ItemType
when 'Equipment' THEN Items.ItemID
else RelatedItems.ItemID
END
from Orders left join OrderDetails
on orders.SOID = orderdetails.soid
left join Items
on orderdetails.ItemID = items.itemid
left join RelatedItems
on orderdetails.ItemID = RelatedItems.RelatedItemID
inner join OrderDetails so
on RelatedItems.ItemID = so.ItemID and Orders.SOID = so.SOID

With an INNER JOIN on OrderDetails so, I lose all of the Equipment items (and, I believe some Accessories that don't have a RelatedItem assigned to them). If I make it a LEFT JOIN, I bring in the Equipment Items, but end up with every RelatedItemID for every accessory, regardless of whether or not the RelatedItemID matches the ItemID for an Equipment item on that sales order (SOID).

I greatly appreciate any time spent helping me with this.

Give this a try:

Wim,

When I try this, I receive this error message:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I have tested it on the sample data you provided in your post from 14:54 and it works on that dataset.

It's next to impossible for me to debug the script without access to your data. But I am convinced that this script may give you another insight on how to tackle this problem. In SQL-land many roads lead to Rome, so your final solution may be quite different from this script. Most likely the script is still buggy, but you might also be stumbling upon faulty data in your database.

Maybe you won't get this error message any more by simply adding DISTINCT between "(SELECT" and "RI.ItemID". But having to add DISTINCT is al too often a good indicator for a flawed script. Use wisely and with caution.

HI, This query may help you

CREATE TABLE #OrderDetails (SOID INT, DetailID INT, ItemID INT)
CREATE TABLE #IREMS (ItemID INT, ItemType VARCHAR(20))
CREATE TABLE #RelatedItems (ItemID INT, RelatedItemID INT)

INSERT INTO #OrderDetails (SOID , DetailID , ItemID )
SELECT 1, 1, 254 UNION ALL
SELECT 1, 2, 260 UNION ALL
SELECT 1, 3, 216 UNION ALL
SELECT 1, 4, 285


INSERT INTO #IREMS (ItemID , ItemType )
SELECT 254, 'Equipment' UNION ALL
SELECT 257, 'Equipment' UNION ALL
SELECT 260, 'Equipment' UNION ALL
SELECT 216, 'Accessory' UNION ALL
SELECT 285, 'Accessory' UNION ALL
SELECT 287, 'Accessory'


INSERT INTO #RelatedItems (ItemID , RelatedItemID )
SELECT 254, 216 UNION ALL
SELECT 254, 285 UNION ALL
SELECT 254, 287 UNION ALL
SELECT 257, 216 UNION ALL
SELECT 260, 287

SELECT DISTINCT O.SOID , O.DetailID , O.ItemID , I.ItemType,
(CASE
WHEN I.ItemID = O.ItemID AND I.ItemID = RI.ItemID THEN O.ItemID
WHEN I.ItemID = O.ItemID AND I.ItemID = RI.RelatedItemID THEN RI.ItemID
ELSE NULL END) AS RelatedItemID
FROM #OrderDetails O
LEFT JOIN #IREMS I ON I.ItemID = O.ItemID
LEFT JOIN #RelatedItems RI ON (RI.ItemID = I.ItemID OR RI.RelatedItemID = I.ItemID)


DROP TABLE #OrderDetails
DROP TABLE #IREMS
DROP TABLE #RelatedItems

sql-programmers,

Thank you for the response. I've tried your query, and it gives me too many values for the RelatedItemID field. This has been part of what makes this query very challenging for me.

The RelatedItemID needs to be:
1) Equal to the ItemID if the item is an Equipment item
2) Equal to the ItemID of the Equipment item in that SOID if its RelatedItems.ItemID is equal to the Items.ItemID of the equipment item
3) NULL (but still presented in the result set) if the Accessory item does not appear in the RelatedItems table.
4) NULL, or anything else (but still present in the result set), in the event that the Accessory item in the SOID does not have a RelatedItems.ItemID that matches an Equipment item in the SOID. Should only be presented once in the result set.

Any help on this would be greatly appreciated.