Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
352 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

4614 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

4614 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  
 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.02 seconds. Powered By: Snitz Forums 2000