Lost on how to write this query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Lost on how to write this query

Author

Topic

Hammerklavier
Starting Member

26 Posts

Posted - 2011-12-06 : 16:38:13

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

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2011-12-06 : 17:05:28

I dont understand. Why shouldn't the following row be in your result

-3 ------------1 -------------1------------5

as Item5 is present in your order.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 23:45:15


select o.OrderNo,o.OrderDetail, ri.ItemID,ri.RelatedItem
from order o
inner join Item i
on i.ItemID = o.ItemID 
inner join RelatedItem ri
on ri.ItemID = i.ItemID 
inner join order o1
on o1.ItemID = ri.RelatedItem
and o1.OrderNo = o.OrderNo 
WHERE o.OrderNo = @yourvalue

@yourvalue is a parameter you add through which you can pass order value (3 in your example)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hammerklavier
Starting Member

26 Posts

Posted - 2011-12-07 : 08:40:41

vijayisonly,

My result set should only show the equipment item that the item is related to. An Accessory item relates to an Equipment item. An Equipment item relates to itself. Thus, each OrderDetail record in my result set has only one RelatedItem.

visakh16,

Thanks for taking a look at this. Unfortunately, I can't use a parameter for the OrderNo field because I need a complete result set for every order, not just one.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 08:52:21

quote:
Originally posted by Hammerklavier

vijayisonly,

My result set should only show the equipment item that the item is related to. An Accessory item relates to an Equipment item. An Equipment item relates to itself. Thus, each OrderDetail record in my result set has only one RelatedItem.

visakh16,

Thanks for taking a look at this. Unfortunately, I can't use a parameter for the OrderNo field because I need a complete result set for every order, not just one.

then dispense with it


select o.OrderNo,o.OrderDetail, ri.ItemID,ri.RelatedItem
from order o
inner join Item i
on i.ItemID = o.ItemID 
inner join RelatedItem ri
on ri.ItemID = i.ItemID 
inner join order o1
on o1.ItemID = ri.RelatedItem
and o1.OrderNo = o.OrderNo

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hammerklavier
Starting Member

26 Posts

Posted - 2011-12-07 : 13:47:48

visakh16,

Is there a way to ensure that I don't lose sales order details where the itemid has no relateditem? Your query seems to work, but I'm losing many sales order records in my result set where I'd simply want the relateditem field to be blank.

Thank you.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-12-07 : 14:33:11

select o.OrderNo,o.OrderDetail, ri.ItemID,ri.RelatedItem
from order o
inner join Item i
on i.ItemID = o.ItemID
LEFTRelatedItem ri
on ri.ItemID = i.ItemID
LEFT join order o1
on o1.ItemID = ri.RelatedItem
and o1.OrderNo = o.OrderNo

Jim

Everyday I learn something that somebody else already knew

Hammerklavier
Starting Member

26 Posts

Posted - 2011-12-07 : 14:48:26

I might be missing something, because this gives me too many records. The number of records should be equal to the number of sales order details (in the case of this example, there would be five records, like the first table I showed.

Hammerklavier
Starting Member

26 Posts

Posted - 2011-12-07 : 16:40:33

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 00:44:01

quote:
Originally posted by Hammerklavier

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.

we're unable to get exact problem from your definition
please put some sample data and explain the scenario where you're missing the records from final resultset

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hammerklavier
Starting Member

26 Posts

Posted - 2011-12-08 : 08:50:45

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).

Does this help to clarify my issues? Please let me know if you have any more questions.

I appreciate people taking the time to help me with this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 09:01:31

do you mean this then?


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
left join OrderDetails so
on RelatedItems.ItemID = so.ItemID 
where Orders.SOID = so.SOID
or so.ItemID is null

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hammerklavier
Starting Member

26 Posts

Posted - 2011-12-08 : 09:21:38

visakh16,

Unfortunately, this still gives me all RelatedItem values for each accessory rather than only giving me the ones where the RelatedItem matches an ItemID of an Equipment item in the sales order.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 09:33:15

[code]
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
left join OrderDetails so
on RelatedItems.ItemID = so.ItemID
where Orders.SOID = so.SOID
or Items.ItemType='Equipment'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hammerklavier
Starting Member

26 Posts

Posted - 2011-12-08 : 09:47:52

visakh16,

This is very close. All that's missing now are the accessory items whose RelatedItem is not in the sales order. They would still need to be displayed, even if it's just a null value. Any idea how to do this?

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 10:00:52

do you mean this?


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
left join OrderDetails so
on RelatedItems.ItemID = so.ItemID 
where Orders.SOID = so.SOID
or Items.ItemType='Equipment'
or so.SOID is null

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hammerklavier
Starting Member

26 Posts

Posted - 2011-12-08 : 10:29:09

While this does bring in the accessories with NULL RelatedItem values, this also brings in accessories that are not a part of the sales order.

If, instead of

or so.SOID is null

I use:

or RelatedItems.ItemID is null

This brings in the accessory items with a NULL relatedItem, which is good. But in doing this, the Accessories whose RelatedItem is not in the sales order are getting left out. Every item on the sales order needs to be included in the result set, even if it doesn't relate to an equipment item in that sales order.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 10:52:55

can you illustrate each of this scenario with a sample data. then i'll try to crack down this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hammerklavier
Starting Member

26 Posts

Posted - 2011-12-08 : 11:26:40

Sure thing.

Here is a sample sales order from the Orders table

SOID----DetailID--ItemID
463-----1---------11714
463-----2---------12042
463-----3---------12269
463-----4---------12030

When I use the query with this WHERE clause...

WHERE Orders.SOID = so.SOID
OR Items.ItemType = 'Equipment'
OR so.SOID IS NULL
This is the result set for that sales order:

SOID----DetailID--ItemID--ItemType-----RelatedItem
463-----1---------11714---Equipment----11714
463-----2---------12042---Accessories--11714
463-----2---------12042---Accessories--11601
463-----2---------12042---Accessories--11823
463-----3---------12269---Accessories--NULL
463-----4---------12030---Accessories--19827
463-----4---------12030---Accessories--19853

The only RelatedItem I should be seeing for DetailID #2 is 11714, since it matches the equipment. For Detail #4, although it doesn't have any RelatedItem values that match the Equipment, I still need it in my result set, but only once, as opposed to being there for each RelatedItem it has.

When I use the query with this WHERE clause...

WHERE Orders.SOID = so.SOID
OR Items.ItemType = 'Equipment'
OR RelatedItems.ItemID IS NULL

This is the result set for that sales order:
SOID----DetailID--ItemID--ItemType-----RelatedItem
463-----1---------11714---Equipment----11714
463-----2---------12042---Accessories--11714
463-----3---------12269---Accessories--NULL

So the fourth DetailID was omitted.

This is the result set I would like to see for this sales order:

SOID----DetailID--ItemID--ItemType-----RelatedItem
463-----1---------11714---Equipment----11714
463-----2---------12042---Accessories--11714
463-----3---------12269---Accessories--NULL
463-----4---------12030---Accessories--"NULL" (string)

Let me know if you have any more questions. I hope this provides better clarity for the problem I am trying to resolve.

Hammerklavier
Starting Member

26 Posts

Posted - 2011-12-09 : 08:55:36

There was a mistake in my previous post (the two WHERE clauses were identical). It should be corrected now.

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources