Help with SQL query - 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

General SQL Server Forums

New to SQL Server Programming

Help with SQL query

Author

Topic

pcc
Starting Member

2 Posts

Posted - 2012-08-02 : 11:17:14

Hi this is my first post so I hope I am in the correct place.
I have a table with 4 fields
Part Spec Status Source

I need to identify all part/spec combinations where the status is ambiguous eg:

Part Spec Status Source
PartA SpecX C A.txt
PartA SpecX C B.txt
PartA SpecX E C.txt
PartB SpecY E D.txt
PartB SpecY E E.txt

In this case, the data suggests that for PartA and SpecX, the status is both C and E, which cannot be the case.

I need to run a query to identify where the same part/spec pairing has different statuses. The source of the file does not matter, although I need to display the source in the output. In this example, the required output will be:

Part Spec Status Source
PartA SpecX C A.txt
PartA SpecX C B.txt
PartA SpecX E C.txt

I'm sure this is fairly simple but I am fairly new to SQL and would appreciate guidance. Hope you can help.
Regards

Pete

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-02 : 11:32:02

[code]
SELECT t.*
FROM Table t
INNER JOIN(
SELECT Part,Spec
FROM table
GROUP BY Part,Spec
HAVING COUNT(DISTINCT Status) >1
)t1
ON t1.Part = t.Part
AND t1.Spec = t.Spec
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pcc
Starting Member

2 Posts

Posted - 2012-08-03 : 03:03:30

Visakh16, thank you very much for your prompt reply. The code is exactly what I needed.
Regards
Pete

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-03 : 10:05:34

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources