SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Learning SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Smylod
Starting Member

3 Posts

Posted - 04/24/2013 :  15:44:41  Show Profile  Reply with Quote
I apologize in advanced if I posted this in the wrong area, but am executing this query via SQL Studio express on a 2008 Server and I need help. 1st year learning SQL!

In any event, pretend I have a 2 column table, I want to query Purchase Orders that have both Part Number 123 and 456, NOT ones that have or.

Purchase_Order Part_Number
-------------- -------------
39529 123
13464 456
13464 123
69898 456

Desire Result:

Purchase_Order Part_Number
-------------- -------------
13464 456
13464 123

Currently, I am executing something like this (see below) and using a pivot table in excel to get my desired result.

SELECT *
FROM yada
WHERE part_number IN ('123','456')

djj55
Constraint Violating Yak Guru

USA
313 Posts

Posted - 04/24/2013 :  15:47:19  Show Profile  Reply with Quote
Are you trying for the minimum Purchase_Order?

djj
Go to Top of Page

Smylod
Starting Member

3 Posts

Posted - 04/24/2013 :  15:49:55  Show Profile  Reply with Quote
Not minimum, but ALL purchase orders that contain BOTH part numbers.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 04/24/2013 :  16:32:48  Show Profile  Reply with Quote
SELECT Purchase_Order
FROM yada
WHERE part_number IN ('123','456')
GROUP BY Purchase_Order
HAVING COUNT(*) = 2
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 04/24/2013 :  16:34:49  Show Profile  Reply with Quote
And here is one way to combine my above query to get the full result set:
SELECT 
	yada.*
FROM 
	yada
INNER JOIN
	(
		SELECT Purchase_Order
		FROM yada
		WHERE part_number IN ('123','456')
		GROUP BY Purchase_Order
		HAVING COUNT(*) = 2
	) AS T
	ON yada.Purchase_Order = T.Purchase_Order
Go to Top of Page

Smylod
Starting Member

3 Posts

Posted - 04/24/2013 :  16:49:10  Show Profile  Reply with Quote
Thank you so much!

Its all a big puzzle :D
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000