Learning SQL - SQL Server Forums

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Learning SQL

Author

Topic

Smylod
Starting Member

3 Posts

Posted - 2013-04-24 : 15:44:41

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

352 Posts

Posted - 2013-04-24 : 15:47:19

Are you trying for the minimum Purchase_Order?

djj

Smylod
Starting Member

3 Posts

Posted - 2013-04-24 : 15:49:55

Not minimum, but ALL purchase orders that contain BOTH part numbers.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-24 : 16:32:48

[code]SELECT Purchase_Order
FROM yada
WHERE part_number IN ('123','456')
GROUP BY Purchase_Order
HAVING COUNT(*) = 2
[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-24 : 16:34:49

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

Smylod
Starting Member

3 Posts

Posted - 2013-04-24 : 16:49:10

Thank you so much!

Its all a big puzzle :D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources