473,326 Members | 2,127 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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!

Jul 23 '05 #1
3 1408
On 11 Apr 2005 21:27:53 -0700, "MaxPenguin"
<ma*************@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?)
Jul 23 '05 #2
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.

Jul 23 '05 #3

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.


Jul 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
3
by: Prem | last post by:
Hi, I am having many problems with inner join. my first problem is : 1) I want to know the precedance while evaluating query with multiple joins. eg. select Employees.FirstName,...
2
by: Sebastian | last post by:
The following query needs about 2 minutes to complete (finding dupes) on a table of about 10000 addresses. Does anyone have an idea on how to speed this up ? Thanks in advance !!! Sebastian
4
by: thilbert | last post by:
All, I have a perplexing problem that I hope someone can help me with. I have the following table struct: Permission ----------------- PermissionId Permission
3
by: Dam | last post by:
Using SqlServer : Query 1 : SELECT def.lID as IdDefinition, TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour FROM serveur.Data_tblDEFINITIONTABLEDECODES def,...
8
by: xixi | last post by:
when i create a join view like this create view JV104FZ.APJTINM1 (APAM32, APNO20, APQY05, PONO01, PONO05, PONO19, POCD01, POCD13, systimestamp, loginname, id ) as select JV104FZ.APPTINM.APAM32,...
3
by: Martin | last post by:
Hello everybody, I have the following question. As a join clause on Oracle we use " table1.field1 = table2.field1 (+) " On SQL Server we use " table1.field1 *= table2.field1 " Does DB2...
2
by: AJ | last post by:
Hi all, I have this monster query (at least i think it is). SELECT c.ID, c.Company_Name, p., 1 As QueryNbr FROM (Company AS c LEFT JOIN Sale AS s ON c.ID = s.Company_ID) LEFT JOIN Package...
4
by: Jane T | last post by:
I appreciate how difficult it is to resolve a problem without all the information but maybe someone has come across a similar problem. I have an 'extract' table which has 1853 rows when I ask for...
5
zachster17
by: zachster17 | last post by:
Hi everyone, First of all, sorry for the massive amount of SQL I am about to type. I have a database that has a "lives" table of insured employees and then another table (that links to the lives...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.