Showing posts with label duplicates. Show all posts
Showing posts with label duplicates. Show all posts

Monday, March 19, 2012

Help with a Join and Duplicates?

Hi All,

I am banging my head against a brick wall over this problem, so any
help in the correct direction would be muchly appreciated!

I have 2 SQL (MS SQL) server tables, realated to -
a Property,
Sales of that property.

A property is uniquely identifed by its Roll, valuation Number and
Suffix (not my choosing).

Each property can only appear in the property table once, and can only
have 1 assessment - but can have multiple sales (ie - over the
annalysis period the same property can sell more than once).

There is approximatly 19000 properties relating to about 8000 sales.

When creating a query to list property and most recent sale (if there
is any) I end up with somthing like this -
SELECT [roll], [valuation], [suffix], [sale date]
FROM [property]
LEFT JOIN [sales]
ON
[property].[roll] = [sales].[roll] AND
[property].[valuation] = [sales].[valuation] AND
[property].[suffix] = [sales].[suffix]
(table names simplifed).

I get rows where there is all the property data there, but sale date
(etc.) is null (as I would expect from a left join), but the problem is
- when there is more than 1 sale for a property it pulls out another
copy of the property data.

In short, because of that I come out with more records than properties.

ie -
roll valuation suffix sale date
12 456789 A 1/1/2003
12 788988 B NULL
14 123456 A 1/1/2003
14 123456 A 1/1/2004
(Note - the last two are the same property).

I didn't know that the left join can affect both joined tables!

Is there any way around this? Any suggestions/hints in the right
direction would be very much appreciated!

THANKS!On 11 Apr 2005 21:27:53 -0700, "MaxPenguin"
<malcolm.lockyer@.origen.co.nz> wrote:

>Hi All,
>
>I am banging my head against a brick wall over this problem, so any
>help in the correct direction would be muchly appreciated!
>
>I have 2 SQL (MS SQL) server tables, realated to -
>a Property,
>Sales of that property.
>
>A property is uniquely identifed by its Roll, valuation Number and
>Suffix (not my choosing).
>Each property can only appear in the property table once, and can only
>have 1 assessment - but can have multiple sales (ie - over the
>annalysis period the same property can sell more than once).
>There is approximatly 19000 properties relating to about 8000 sales.
>
>When creating a query to list property and most recent sale (if there
>is any) I end up with somthing like this -
>SELECT [roll], [valuation], [suffix], [sale date]
>FROM [property]
>LEFT JOIN [sales]
>ON
>[property].[roll] = [sales].[roll] AND
>[property].[valuation] = [sales].[valuation] AND
>[property].[suffix] = [sales].[suffix]
>(table names simplifed).
>I get rows where there is all the property data there, but sale date
>(etc.) is null (as I would expect from a left join), but the problem is
>- when there is more than 1 sale for a property it pulls out another
>copy of the property data.
>In short, because of that I come out with more records than properties.
>ie -
>roll valuation suffix sale date
>12 456789 A 1/1/2003
>12 788988 B NULL
>14 123456 A 1/1/2003
>14 123456 A 1/1/2004
>(Note - the last two are the same property).
>
>I didn't know that the left join can affect both joined tables!
>
>Is there any way around this? Any suggestions/hints in the right
>direction would be very much appreciated!
>THANKS!

Are you able to tell us what want the query to return?
(And why?)|||It isn't really a matter of the left join "affecting both tables". For
any join that you use, if you have a many to one relationship then you
are going to get multiple rows for the "one" side since the join
effectively creates a cartesian product to start with. Whittling that
down is a matter of your join criteria.

In your description of the problem you state that you want the "most
recent sale" but there is nothing in your query to try to limit the
results to the most recent sale. Either of the queries below should
work. I usually see better performance using the LEFT JOIN/IS NOT NULL
method, but using NOT EXISTS is a bit more readable/logical in my
opinion.

SELECT p.roll, p.valuation, p.suffix, s.[sale date]
FROM property p
LEFT JOIN sales s ON p.roll = s.roll
AND p.valuation = s.valuation
AND p.suffix = s.suffix
LEFT JOIN sales s2 ON s2.roll = s.roll
AND s2.valuation = s.valuation
AND s2.suffix = s.suffix
AND s2.[sale date] > s.[sale date]
WHERE s2.roll IS NULL

SELECT p.roll, p.valuation, p.suffix, s.[sale date]
FROM property p
LEFT JOIN sales s ON p.roll = s.roll
AND p.valuation = s.valuation
AND p.suffix = s.suffix
WHERE NOT EXISTS (SELECT *
FROM sales s2
WHERE s2.valuation = s.valuation
AND s2.suffix = s.suffix
AND s2.roll = s.roll
AND s2.[sale date] > s.[sale date])

Both methods assume that you cannot have two sales on the same exact
date for the same property. They both effectively remove any rows where
there is another row for the same property with a later sale date. That
will leave only those rows with the latest (most recent) sale date.

HTH,
-Tom.|||Thomas R. Hummel wrote:
> In your description of the problem you state that you want the "most
> recent sale" but there is nothing in your query to try to limit the
> results to the most recent sale. Either of the queries below should
> work. I usually see better performance using the LEFT JOIN/IS NOT
NULL
> method, but using NOT EXISTS is a bit more readable/logical in my
> opinion.

Thanks your solution works perfectly. Thats exactly what I needed
really - a way to tell it that I did just want the most recent sales.

Thanks again :)!

> SELECT p.roll, p.valuation, p.suffix, s.[sale date]
> FROM property p
> LEFT JOIN sales s ON p.roll = s.roll
> AND p.valuation = s.valuation
> AND p.suffix = s.suffix
> LEFT JOIN sales s2 ON s2.roll = s.roll
> AND s2.valuation = s.valuation
> AND s2.suffix = s.suffix
> AND s2.[sale date] > s.[sale date]
> WHERE s2.roll IS NULL
> SELECT p.roll, p.valuation, p.suffix, s.[sale date]
> FROM property p
> LEFT JOIN sales s ON p.roll = s.roll
> AND p.valuation = s.valuation
> AND p.suffix = s.suffix
> WHERE NOT EXISTS (SELECT *
> FROM sales s2
> WHERE s2.valuation = s.valuation
> AND s2.suffix = s.suffix
> AND s2.roll = s.roll
> AND s2.[sale date] > s.[sale date])
> Both methods assume that you cannot have two sales on the same exact
> date for the same property. They both effectively remove any rows
where
> there is another row for the same property with a later sale date.
That
> will leave only those rows with the latest (most recent) sale date.
> HTH,
> -Tom.

Monday, March 12, 2012

Help wit Query to list duplicates

I am trying to create a list of those cases where a specific field value was
duplicate with a set of rows. The query I am trying is shown below (it is
listing all rows instead of just those where there is a duplicate value in
the field Eqp1Voc.
ID is an unique key for each row.
What did I overlook?
Wayne
========================================
=
SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
WHERE ID IN
(SELECT B.ID
FROM DCScores A JOIN DCScores B
ON A.ID <> B.ID
AND A.Eqp1Voc = B.Eqp1Voc)
And CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Order By Eqp1Voc DESC
========================================
=> SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
> WHERE ID IN
(SELECT B.ID
FROM DCScores
Group by B.ID
HAVING COUNT(Eqp1Voc) >1)
> And CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
news:O3x$k%23DQFHA.356@.TK2MSFTNGP14.phx.gbl...
>I am trying to create a list of those cases where a specific field value
>was
> duplicate with a set of rows. The query I am trying is shown below (it is
> listing all rows instead of just those where there is a duplicate value in
> the field Eqp1Voc.
> ID is an unique key for each row.
> What did I overlook?
> Wayne
>
> ========================================
=
> SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
> WHERE ID IN
> (SELECT B.ID
> FROM DCScores A JOIN DCScores B
> ON A.ID <> B.ID
> AND A.Eqp1Voc = B.Eqp1Voc)
> And CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
> ========================================
=
>|||Thanks for the quick response but that doesn't return any rows (and I know
there are duplicates in that field!) With your suggestion, the query now
looks like this:
SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
WHERE ID IN
(SELECT B.ID
FROM DCScores B
Group by B.ID
HAVING COUNT(Eqp1Voc) >1)
And CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Order By Eqp1Voc DESC
Wayne
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%238nw0CEQFHA.688@.TK2MSFTNGP14.phx.gbl...
> (SELECT B.ID
> FROM DCScores
> Group by B.ID
> HAVING COUNT(Eqp1Voc) >1)
> HTH, Jens Smeyer
> --
> http://www.sqlserver2005.de
> --
>
> "Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
> news:O3x$k%23DQFHA.356@.TK2MSFTNGP14.phx.gbl...
is
in
>|||Sorry for that missed up something...
Here it goes:
SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
WHERE Eqp1Voc IN
(SELECT Eqp1Voc
FROM DCScores
Group by Eqp1Voc
HAVING COUNT(Eqp1Voc) > 1)
And CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Order By Eqp1Voc DESC
Jens.
"Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
news:O3x$k%23DQFHA.356@.TK2MSFTNGP14.phx.gbl...
>I am trying to create a list of those cases where a specific field value
>was
> duplicate with a set of rows. The query I am trying is shown below (it is
> listing all rows instead of just those where there is a duplicate value in
> the field Eqp1Voc.
> ID is an unique key for each row.
> What did I overlook?
> Wayne
>
> ========================================
=
> SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
> WHERE ID IN
> (SELECT B.ID
> FROM DCScores A JOIN DCScores B
> ON A.ID <> B.ID
> AND A.Eqp1Voc = B.Eqp1Voc)
> And CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
> ========================================
=
>|||Jens;
Thanks again but with that change, it returns all rows, not just those with
a duplicate value?
Wayne
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23FGm0YEQFHA.204@.TK2MSFTNGP15.phx.gbl...
> Sorry for that missed up something...
> Here it goes:
> SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
> WHERE Eqp1Voc IN
> (SELECT Eqp1Voc
> FROM DCScores
> Group by Eqp1Voc
> HAVING COUNT(Eqp1Voc) > 1)
> And CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
>
> Jens.
>
> "Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
> news:O3x$k%23DQFHA.356@.TK2MSFTNGP14.phx.gbl...
is
in
>|||Try this:
SELECT UnitName, Eqp1Judge,
Eqp1Voc
FROM DCScores S
Where Exists
(Select * From DCScores
Where Eqp1Voc = S.Eqp1Voc
Group By Eqp1Voc
Having Count(*) > 1)
WHERE CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Order By Eqp1Voc DESC
"Wayne Wengert" wrote:

> I am trying to create a list of those cases where a specific field value w
as
> duplicate with a set of rows. The query I am trying is shown below (it is
> listing all rows instead of just those where there is a duplicate value in
> the field Eqp1Voc.
> ID is an unique key for each row.
> What did I overlook?
> Wayne
>
> ========================================
=
> SELECT UnitName, Eqp1Judge, Eqp1Voc FROM DCScores
> WHERE ID IN
> (SELECT B.ID
> FROM DCScores A JOIN DCScores B
> ON A.ID <> B.ID
> AND A.Eqp1Voc = B.Eqp1Voc)
> And CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
> ========================================
=
>
>|||Thanks for the suggestion but that yields an error: "Incorrect syntax near
the keyword 'WHERE'."
Wayne
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:F52016E3-1BE3-48A4-9A3D-193DA51896CD@.microsoft.com...
> Try this:
> SELECT UnitName, Eqp1Judge,
> Eqp1Voc
> FROM DCScores S
> Where Exists
> (Select * From DCScores
> Where Eqp1Voc = S.Eqp1Voc
> Group By Eqp1Voc
> Having Count(*) > 1)
> WHERE CircuitID = 501
> AND PorF = 'P'
> AND UnitClass = 'SW'
> Order By Eqp1Voc DESC
>
> "Wayne Wengert" wrote:
>
was
is
in|||Replace this "WHERE CircuitID" WITH "AND CircuitID"
Jens Smeyer.
"Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
news:u7e0HAFQFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Thanks for the suggestion but that yields an error: "Incorrect syntax near
> the keyword 'WHERE'."
> Wayne
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:F52016E3-1BE3-48A4-9A3D-193DA51896CD@.microsoft.com...
> was
> is
> in
>|||Jens;
I took that advice and also realized that the Where clause needs to be
applied to that inner select. I changed it to the following and now it
works.
I appreciate all the help.
==================================
SELECT UnitName, Eqp1Judge,
Eqp1Voc
FROM DCScores S
Where Exists
(Select * From DCScores
Where Eqp1Voc = S.Eqp1Voc
AND CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Group By Eqp1Voc
Having Count(*) > 1)
AND CircuitID = 501
AND PorF = 'P'
AND UnitClass = 'SW'
Order By Eqp1Voc DESC
==================================
Wayne
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23KCoZCFQFHA.2380@.TK2MSFTNGP10.phx.gbl...
> Replace this "WHERE CircuitID" WITH "AND CircuitID"
> Jens Smeyer.
> "Wayne Wengert" <wayneDONTWANTSPAM@.wengert.com> schrieb im Newsbeitrag
> news:u7e0HAFQFHA.1176@.TK2MSFTNGP12.phx.gbl...
near
(it
value
>