Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

Help with SQL Query

Experts,


I am trying to get the list of Items and couple of other details from Item_details table based on two primary conditions from other two tables I have joined. Please see the code below.

1. If Itemcoverage from Item_details is 1 (please note that there are rows for the items with Itemcoverage as "0" as well)  
2. And there is no row in Item_History table with ItemUpdateID "9"

(In other words, I need to find out the Items that are having Item_details.Itemcoverage = '1' and doesn't have a row in the Item_History table with Item_History.ItemUpdateID not in (9))

select I.itemNumber,I.ItemName,I.ItemCode From MyDatabase..Item I
inner join MyDatabase..Item_details ID
on I.ItemID = ID.ItemID 
inner join MyDatabase..Item_History IH
on I.ItemID = IH.ItemID  
where I.ItemType = 6 and ID.Itemcoverage = '1' and IH.ItemUpdateID not in (9) 

Open in new window


Thanks in advance
0
Tpaul_10
Asked:
Tpaul_10
4 Solutions
 
DultonCommented:
try a not exists for the item history portion, the rest should be able to be accomplished by the joins.

select I.itemNumber,I.ItemName,I.ItemCode
 From MyDatabase..Item I
inner join MyDatabase..Item_details ID
on I.ItemID = ID.ItemID   
where I.ItemType = 6 and ID.Itemcoverage = '1' 
and not exists (select IH.ItemUpdateId FROM MyDatabase..Item_History AS IH
WHERE IH.ItemID = I.ItemID AND IH.ItemUpdateID = 9)

Open in new window

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
you can do that during the join and won't require a subquery:

select I.itemNumber,I.ItemName,I.ItemCode From MyDatabase..Item I
inner join MyDatabase..Item_details ID
on I.ItemID = ID.ItemID and ID.Itemcoverage = '1'
inner join MyDatabase..Item_History IH
on I.ItemID = IH.ItemID   and IH.ItemUpdateID not in (9) -- '9' ?
where I.ItemType = 6
0
 
John_VidmarCommented:
SELECT	I.itemNumber
,	I.ItemName
,	I.ItemCode
FROM	MyDatabase..Item I
WHERE	EXISTS
	(	SELECT	*
		FROM	MyDatabase..Item_details
		WHERE	ItemID = I.ItemID 
		AND	Itemcoverage = '1'
	)
AND	NOT EXISTS
	(	SELECT	*
		FROM	MyDatabase..Item_History
		WHERE	ItemID = I.ItemID  
		AND	ItemUpdateID = 9
	)

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ste5anSenior DeveloperCommented:
As the history may contain multiple lines per item, a JOIN is imho not optimal. I would use the EXISTS() predicate. E.g.

SELECT	I.itemNumber,
	I.ItemName,
	I.ItemCode 
FROM	MyDatabase..Item I
	INNER JOIN MyDatabase..Item_details ID ON I.ItemID = ID.ItemID 
WHERE I.ItemType = 6 
	AND ID.Itemcoverage = '1' 
	AND NOT EXISTS 
	(
		SELECT	*
		FROM	MyDatabase..Item_History IH 
		WHERE	I.ItemID = IH.ItemID  
			AND IH.ItemUpdateID = 9
	);

Open in new window


An addtional index would be also good:

CREATE NONCLUSTERED INDEX IX_Item_History ON MyDatabase..Item_History 
(
	ItemUpdateID,
	ItemID
);

Open in new window

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
As the history may contain multiple lines per item, a JOIN is imho not optimal
The execution plan is the same for the inner join versus the exist.

http://www.exacthelp.com/2012/10/subquery-vs-inner-join-which-one-is.html
0
 
ste5anSenior DeveloperCommented:
Nope, not generally. It depends on the concrete model, indices and statistics. With a million rows in each table I get this differences:

Actual execution planTableDDL.sql
CompareJoinExists.sql
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
compare

this (yours):
SELECT	I.ItemNumber,
		I.ItemName,
		I.ItemCode		
FROM	Items I
	INNER JOIN ItemDetails ID ON I.ItemID = ID.ItemID 
WHERE I.ItemType = 6 
	AND ID.Itemcoverage = '1' 
	AND NOT EXISTS 
	(
		SELECT	*
		FROM	ItemHistory IH 
		WHERE	I.ItemID = IH.ItemID  
			AND IH.ItemUpdateID = 9
	);		                        

Open in new window


with mine:

SELECT	I.ItemNumber,
		I.ItemName,
		I.ItemCode		
FROM	Items I
	INNER JOIN ItemDetails ID ON I.ItemID = ID.ItemID 
                inner  join ItemHistory IH on id.ItemID = IH.ItemId and IH.ItemUpdateID  not in (9)
WHERE I.ItemType = 6 
	AND ID.Itemcoverage = '1' 
                        

Open in new window

0
 
ste5anSenior DeveloperCommented:
As you may see in the plan, they are the same. But the other using only [NOT] EXISTS is not. The point is: It's not a general rule. As always: you need to compare.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now