Challenge revisisted

Hi,
I'm trying to port som old access code to asp.net and the access solution ha
s a couple of queries to sql server that I'm not sure work as expected. I t
hink the idea of the query is to identify if the same event (slaugher of rei
ndeer) is registered in two
different places (field 5 in my last challenge), but two duplicate rows diff
ering only by place does not get selected by the query.
The query is as follows:
SELECT ImportID FROM Temp t WHERE ( EXISTS (
SELECT Year, Code1, Code2, Date FROM (
SELECT DISTINCT Year, Code1, Code2, Date , Place FROM Temp ) SUBQUERY
WHERE ( t.Year = SUBQUERY.Year )
AND ( t.Code1 = SUBQUERY.Code1 )
AND ( t.Code2 = SUBQUERY.Code2 )
AND ( t.Date = SUBQUERY.Date)
GROUP BY Year, Code1, Code2, Date
HAVING (COUNT(*) > 1) ))
Can anyone see if this query does anything useful?, or perhaps give me a que
ry that selects rows with same Year,Code1,Code2,Date, but with different Pla
ce.
The other query is essentially the same, using Zone instead of Place
Morten WennevikOn Mon, 14 Nov 2005 08:51:43 +0100, Morten Wennevik wrote:

>Hi,
>I'm trying to port som old access code to asp.net and the access solution has a cou
ple of queries to sql server that I'm not sure work as expected. I think the idea o
f the query is to identify if the same event (slaugher of reindeer) is registered in
tw
o different places (field 5 in my last challenge), but two duplicate rows differing only by
place does not get selected by the query.
>The query is as follows:
>SELECT ImportID FROM Temp t WHERE ( EXISTS (
> SELECT Year, Code1, Code2, Date FROM (
> SELECT DISTINCT Year, Code1, Code2, Date , Place FROM Temp ) SUBQUERY
> WHERE ( t.Year = SUBQUERY.Year )
> AND ( t.Code1 = SUBQUERY.Code1 )
> AND ( t.Code2 = SUBQUERY.Code2 )
> AND ( t.Date = SUBQUERY.Date)
> GROUP BY Year, Code1, Code2, Date
> HAVING (COUNT(*) > 1) ))
>Can anyone see if this query does anything useful?, or perhaps give me a qu
ery that selects rows with same Year,Code1,Code2,Date, but with different Pl
ace.
>The other query is essentially the same, using Zone instead of Place
>Morten Wennevik
Hi Morten,
I'm not sure what you want eactly. If you want to find year / code1 /
code2 / date combinations for which more than one row exist, use:
SELECT Year, Code1, Code2, Date, COUNT(*)
FROM Temp
GROUP BY Year, Code1, Code2, Date
HAVING COUNT(*) > 1
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)