|
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
Problems with slight differences in two 'duplicate' records
I am having a tricky problem. I have records in a database that are in pairs based on a composite key (3 fields).
Some of the pairs are completely identical, meaning each and every field in those pairs are the same. Some, however have a slight difference, in that one of the fields was updated and does not make the pairs truly identical. Let's assume a table like this: Table Name - FOOD Fields: (RecID, Name, Type, Color, Size) And the table structure is laid out like so: 1, Apple, Fruit, Red, Small 1, Apple, Fruit, Green, Small 2, Grape, Fruit, Purple, Small 2, Grape, Fruit, Purple, Small 3, Pineapple, Fruit, Brown, Small 3, Pineapple, Fruit, Brown, Large What I want to do is write a query that will show me only the pairs that have changes in them. When I run the query I would want to see the following: 1, Apple, Fruit, Red, Small 1, Apple, Fruit, Green, Small 3, Pineapple, Fruit, Brown, Small 3, Pineapple, Fruit, Brown, Large I would not want to see #2 (Grapes) because the records are identical. Might I have to use a self join? Any help would be cool. Sanctos |
|
#2
|
||||
|
||||
|
Select Distinct RecID, Name, Type, Colour, Size
FROM table |
|
#3
|
|||
|
|||
|
Select Distinct RecID, Name, Type, Colour, Size FROM table
WON'T work!!!! It will display all the lines that are not identical, but also display the ones that are the same one time If I read the problem correctly this is not wanted. What you need is something like this: select * from table a where exists (select * from table b where b.row_id=a.row_id and (a.name<>b.name or a.type<>b.type or a.colour<>b.colour or a.size<>b.size)) |
![]() |
| Viewing: ASP Free Forums > Database > Microsoft SQL Server > Problems with slight differences in two 'duplicate' records |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|