Solved

Help with SQL Query

Posted on 2014-04-16
8
231 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
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
 
LVL 33

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 33

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 33

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now