Hi,

Can anyone tell me if this piece of code does anything useful?


SELECT * from TempTable  t WHERE (EXISTS (

SELECT Field1, Field2, Field3, Field4 FROM
(SELECT DISTINCT Field1, Field2, Field3, Field4, Field5
FROM TempTable) SUBQUERY

WHERE (t.Field1 = SUBQUERY.Field1)
And (t.Field2 = SUBQUERY.Field2)
AND (t.Field3 = SUBQUERY.Field3)
AND (t.Field4 = SUBQUERY.Field4)

GROUP BY Field1, Field2, Field3, Field4 ))
HAVING (COUNT(*) > 1) ))



Another thing I'm wondering about is why this code returns 913 results

SELECT * from TempTable  t WHERE (EXISTS (

SELECT Field1, Field2, Field3, Field4 FROM
(SELECT DISTINCT Field1, Field2, Field3, Field4, Field5
FROM TempTable) SUBQUERY

WHERE (t.Field1 = SUBQUERY.Field1)
And (t.Field2 = SUBQUERY.Field2)
AND (t.Field3 = SUBQUERY.Field3)
AND (t.Field4 = SUBQUERY.Field4) ))

While this code returns 910

SELECT * FROM TempTable AS I,
(SELECT Field1, Field2, Field3, Field4 FROM ImportSlaktTemp
GROUP BY Field1, Field2, Field3, Field4 HAVING COUNT(*) > 1) AS O
WHERE I.Field1 = O.Field1
AND I.Field2 = O.Field2
AND I.Field3 = O.Field3
AND I.Field4 = O.Field4


As you might gather I'm not all that experienced in SQL-scripts so any insight is welcome

Morten

Looks like it could, hard to say if it would.
It might help the most if you put in your post,
Some DDL and some sample data,
Expected results and a big baked potato.


ML

there is too many )), but I hope these on GROUP BY row arent there

It selects all records, that have more different (coresponding) rows with
same values in field1..field4, but not same in field5.

if you have data like
1 1 1 1 1  
1 1 1 1 1
1 1 1 2 1
1 1 1 2 2
1 1 1 2 2


so SUBQUERY (as SELECT DISTINCT) gets
1 1 1 1 1
1 1 1 2 1
1 1 1 2 2

now SELECTs from this field1..field4
1 1 1 1
1 1 1 2
1 1 1 2

GROUP BY them and let only those who have more then 1 same (HAVING count > 1)

1 1 1 2   .... (count(*)=2)

now SELECTs those records from all TempTable (full data) for which EXISTs
ekvivatent in last written result. So result will be

1 1 1 2 1
1 1 1 2 2
1 1 1 2 2

For each of these row exists at least one another (different) row with same
field1..field4, and different field5.

Explanation isn't technicaty correct, cause this SQL works a little bit
different way to get this results (for each record from TempTable, DB is
asked if it has another similar row, so in my 3rd result will not be records
that belongs to other groups) , but I hope my explanation is more clearly
(and correct also :) ).

Show quoteHide quote

just for complete info. Each row that isnt listed in result (but belongs to
TempTable) has NO such coresponding row.

These are not listed
1 1 1 1 1
1 1 1 1 1

because there is NO record in TempTable that has same field1..field4 and
different field5.

Thanks,

I'm still not sure why the code runs but at least it is somewhat clearer what they tried to accomplish with it.

Morten

How to calculate comparisons of fields between records
Deleting large amount of data on sql server 2000
Order problem
Dynamic SQL
SQL Server 2005 & Visual Studio 205 RC