sql server programming:
I am trying to get a list of rows where certain fields are duplicated. The
following query works fine:
SELECT Names.NameID, Names.Addr1, Names.ZIP, Names.FirstName,
Names.LastName,
Names.City, Names.StateProv FROM Names JOIN (SELECT Addr1, ZIP FROM Names
GROUP BY Addr1, ZIP HAVING count(*) > 1)
AS Duplicates ON Duplicates.Addr1 = Names.Addr1
AND Duplicates.ZIP = Names.ZIP Order by Names.zip, Names.Addr1
But I also want to be able to specify the starting ZIP code which I tried to
do by adding a WHERE clause as in the following:
SELECT Names.NameID, Names.Addr1, Names.ZIP, Names.FirstName,
Names.LastName,
Names.City, Names.StateProv FROM Names Where (Names.ZIP > '00001')
JOIN (SELECT Addr1, ZIP FROM Names GROUP BY Addr1, ZIP HAVING count(*) > 1)
AS Duplicates ON Duplicates.Addr1 = Names.Addr1
AND Duplicates.ZIP = Names.ZIP Order by Names.zip, Names.Addr1
This query fails with syntax errors "near JOIN"
What is the correct way way to specify a WHERE clause in this type of query?
--
------------------------------------
Wayne Wengert
wayne@wengert.org
Hi Try: SELECT N.NameID, N.Addr1, N.ZIP, N.FirstName, N.LastName, N.City, N.StateProv FROM Names N JOIN (SELECT Addr1, ZIP FROM Names GROUP BY Addr1, ZIP HAVING count(*) > 1) AS D ON D.Addr1 = N.Addr1 AND D.ZIP = N.ZIP WHERE N.ZIP > '00001' ORDER BY N.zip, N.Addr1 John [quoted text, click to view] "Wayne Wengert" <wayne@wengert.org> wrote in message
news:O0ITOnleDHA.4024@TK2MSFTNGP11.phx.gbl...
> I am trying to get a list of rows where certain fields are duplicated. The
> following query works fine:
>
> SELECT Names.NameID, Names.Addr1, Names.ZIP, Names.FirstName,
> Names.LastName,
> Names.City, Names.StateProv FROM Names JOIN (SELECT Addr1, ZIP FROM Names
> GROUP BY Addr1, ZIP HAVING count(*) > 1)
> AS Duplicates ON Duplicates.Addr1 = Names.Addr1
> AND Duplicates.ZIP = Names.ZIP Order by Names.zip, Names.Addr1
>
> But I also want to be able to specify the starting ZIP code which I tried
to
> do by adding a WHERE clause as in the following:
>
> SELECT Names.NameID, Names.Addr1, Names.ZIP, Names.FirstName,
> Names.LastName,
> Names.City, Names.StateProv FROM Names Where (Names.ZIP > '00001')
> JOIN (SELECT Addr1, ZIP FROM Names GROUP BY Addr1, ZIP HAVING count(*) >
1)
> AS Duplicates ON Duplicates.Addr1 = Names.Addr1
> AND Duplicates.ZIP = Names.ZIP Order by Names.zip, Names.Addr1
>
> This query fails with syntax errors "near JOIN"
>
> What is the correct way way to specify a WHERE clause in this type of
query?
> --
> ------------------------------------
> Wayne Wengert
> wayne@wengert.org
>
>
Hi, [quoted text, click to view] > SELECT Names.NameID, Names.Addr1, Names.ZIP, Names.FirstName,
> Names.LastName,
> Names.City, Names.StateProv FROM Names Where (Names.ZIP > '00001')
NOT HERE
> JOIN (SELECT Addr1, ZIP FROM Names GROUP BY Addr1, ZIP HAVING count(*) >
1)
> AS Duplicates ON Duplicates.Addr1 = Names.Addr1
> AND Duplicates.ZIP = Names.ZIP Order by Names.zip, Names.Addr1
TRY LAST
> AND Duplicates.ZIP = Names.ZIP Order by Names.zip, Names.Addr1
Where (Names.ZIP > '00001') ------- SHINICHI YONEDA MXL04371@nifty.ne.jp Microsoft Most Valuable Professional MVP for SQL Server 2002-2003 [quoted text, click to view] "Wayne Wengert" <wayne@wengert.org> wrote in message
news:O0ITOnleDHA.4024@TK2MSFTNGP11.phx.gbl...
> I am trying to get a list of rows where certain fields are duplicated. The
> following query works fine:
>
> SELECT Names.NameID, Names.Addr1, Names.ZIP, Names.FirstName,
> Names.LastName,
> Names.City, Names.StateProv FROM Names JOIN (SELECT Addr1, ZIP FROM Names
> GROUP BY Addr1, ZIP HAVING count(*) > 1)
> AS Duplicates ON Duplicates.Addr1 = Names.Addr1
> AND Duplicates.ZIP = Names.ZIP Order by Names.zip, Names.Addr1
>
> But I also want to be able to specify the starting ZIP code which I tried
to
> do by adding a WHERE clause as in the following:
>
> SELECT Names.NameID, Names.Addr1, Names.ZIP, Names.FirstName,
> Names.LastName,
> Names.City, Names.StateProv FROM Names Where (Names.ZIP > '00001')
> JOIN (SELECT Addr1, ZIP FROM Names GROUP BY Addr1, ZIP HAVING count(*) >
1)
> AS Duplicates ON Duplicates.Addr1 = Names.Addr1
> AND Duplicates.ZIP = Names.ZIP Order by Names.zip, Names.Addr1
>
> This query fails with syntax errors "near JOIN"
>
> What is the correct way way to specify a WHERE clause in this type of
query?
> --
> ------------------------------------
> Wayne Wengert
> wayne@wengert.org
>
>
Thanks guys! Wayne [quoted text, click to view] "Wayne Wengert" <wayne@wengert.org> wrote in message
news:O0ITOnleDHA.4024@TK2MSFTNGP11.phx.gbl...
> I am trying to get a list of rows where certain fields are duplicated. The
> following query works fine:
>
> SELECT Names.NameID, Names.Addr1, Names.ZIP, Names.FirstName,
> Names.LastName,
> Names.City, Names.StateProv FROM Names JOIN (SELECT Addr1, ZIP FROM Names
> GROUP BY Addr1, ZIP HAVING count(*) > 1)
> AS Duplicates ON Duplicates.Addr1 = Names.Addr1
> AND Duplicates.ZIP = Names.ZIP Order by Names.zip, Names.Addr1
>
> But I also want to be able to specify the starting ZIP code which I tried
to
> do by adding a WHERE clause as in the following:
>
> SELECT Names.NameID, Names.Addr1, Names.ZIP, Names.FirstName,
> Names.LastName,
> Names.City, Names.StateProv FROM Names Where (Names.ZIP > '00001')
> JOIN (SELECT Addr1, ZIP FROM Names GROUP BY Addr1, ZIP HAVING count(*) >
1)
> AS Duplicates ON Duplicates.Addr1 = Names.Addr1
> AND Duplicates.ZIP = Names.ZIP Order by Names.zip, Names.Addr1
>
> This query fails with syntax errors "near JOIN"
>
> What is the correct way way to specify a WHERE clause in this type of
query?
> --
> ------------------------------------
> Wayne Wengert
> wayne@wengert.org
>
>
Don't see what you're looking for? Try a search.
|
