Solved

Help with SQL Query

Posted on 2014-04-16
8
236 Views
Last Modified: 2014-04-21
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
Comment
Question by:Tpaul_10
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 6

Accepted Solution

by:
Dulton earned 150 total points
ID: 40004794
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
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40004796
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
 
LVL 11

Assisted Solution

by:John_Vidmar
John_Vidmar earned 50 total points
ID: 40004822
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
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!

 
LVL 34

Expert Comment

by:ste5an
ID: 40004823
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
 
LVL 40

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 50 total points
ID: 40004919
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
 
LVL 34

Assisted Solution

by:ste5an
ste5an earned 50 total points
ID: 40007363
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
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40007580
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
 
LVL 34

Expert Comment

by:ste5an
ID: 40007588
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question