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

Author

Topic

black_pearl
Starting Member

15 Posts

Posted - 2006-05-14 : 20:51:09

hello,
i have a table here:
serial__________code
5U0604T01561| 3607.0
5U0604T01561| 0.0
5U0604T01561| 0.0
5U0604T01728| 0.0
5U0604T01728| 0.0
5U0604T01736| 0.0

how can i select the serial which has a code 0.0 and has a dulpicate but its duplicate is also code 0.0, not a nonzero.
for example: i want to select the serial 5U0604T01728 because it appeared twice and both codes are 0.0. but i dont want to select the serial 5U0604T01561 because though it appeared many times and two of their codes are 0.0, one was a nonzero. pls help me with my query. hope my explaination wasnt confusing.

thanks a lot.

__black_pearl__

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-05-14 : 22:41:53

[code]
DECLARE @table TABLE(serial VARCHAR(55), code DECIMAL(6,1))

INSERT @table(serial, code)
SELECT '5U0604T01561','3607.0' UNION ALL
SELECT '5U0604T01561','0.0' UNION ALL
SELECT '5U0604T01561','0.0' UNION ALL
SELECT '5U0604T01728','0.0' UNION ALL
SELECT '5U0604T01728','0.0' UNION ALL
SELECT '5U0604T01736','0.0'

SELECT serial, code FROM @table

SELECT
t.serial
FROM
@table t
LEFT OUTER JOIN (
SELECT serial
FROM @table
WHERE code <> 0.0) dt ON t.serial = dt.serial
WHERE
dt.serial IS NULL
GROUP BY
t.serial,
t.code
HAVING
COUNT(t.serial)>1
[/code]

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.