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
Solved

Help with SQL Query

Posted on 2014-04-16
8
234 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

789 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