Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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