Locating duplicates ... sort of

[Pipe2Path]

I have a table called Machine 2 of whose columns are called MachineSerial and MachineModel. I've got values like V4 and V4-2B in the MachineModel column. I would like to know all records that have either a V4 or V4-2B in the MachineModel column that have the same serial number in the MachineSerial column.

Any ideas on the SQL query on this?

I tried the following:

select MachineSerial, MachineModel
from Machine
where (MachineModel = 'V4' or MachineModel = 'V4-2B')
group by MachineSerial, MachineModel
having count(MachineSerial) > 1

This doesn't give the desired result.

Thanks in advance

 

[pankajv]

Could Be a round about way, but should work

SELECT MachineCode, MachineSerial
FROM Machine M
INNER JOIN
(
SELECT MachineSerial
FROM
(
SELECT MachineSerial
FROM Machine
WHERE MachineModel LIKE 'V4'
)V4
INNER JOIN
(
SELECT MachineSerial
FROM Machine
WHERE MachineModel LIKE 'V4-2B'
)[V4-2B]
ON V4.MachineSerial = [V4-2B].MachineSerial
)MS
ON M.SerialNumber = MS.SerialNumber
WHERE M.MachineModel LIKE 'V4' or M.MachineModel LIKE 'V4-2B'

 

[r937]

select MachineSerial
from Machine
where (MachineModel = 'V4' or MachineModel = 'V4-2B')
group by MachineSerial
having count(*) > 1


rudy