Left Outer Join displaying multiple records of the same row

[gradinumcp]

Hi! I have 2 tables Projects_Units and customers and I am doing a LEFT OUTER JOIN. The customers table has duplicate entries--like TOM SELLECK appears 5 times.

When I am displaying the records of the LEFT OUTER JOIN, it shows the duplicate entries--so TOM SELLECK still appears 5 times on my ASP Page.

How do i do something like DISTINCT or UNIQUE to it. Someone suggested i remove the LEFT OUTER JOIN and then have a "Unique" SELECT--any ideas what that means???

HERES the query:-

mysql="SELECT * From Project_Units left outer join lcdb.dbo.customers on Project_Units.Contract_Holder=lcdb.dbo.customers.customerid left outer join Project_Buildings on Project_Units.Building_ID=Project_Buildings.Building_ID WHERE Project_Units.ProjectID='"& request.cookies("EFC")("Condo") &"' and unit_number <> '0' order by Building_Number ,floor, Unit_number"
set Cust=cnn.execute(mysql)

 

[azzazzello]

why not just add a GROUP BY to the query?

say your TOM SELLECK column is called "name". Add *GROUP BY name* before your "ORDER BY ..."

 

[r937]

azzy, you cannot "just add a GROUP BY"

that way lies madness

never mind that mysql lets you run invalid GROUP BY queries, you should only ever write valid queries



gradinumcp, your customer table surely does not have the same customer multiple times, so if you are seeing TOM SELLECK multiple times, it's because there are multiple rows with the same name returned by your join

what you should do is analyse why this is happening, not how to suppress it

(hint: DISTINCT won't do this job)

please show a (small) sample of result rows from your query and let's see if we can figure it out

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[azzazzello]

I just wanted to remedy the problem I was not looking whether the query was proper or improper. Nothing wrong with a little madness

 

[gradinumcp]

Hey guys thanks for your feedback--here's 2 sets of results from the query:

Turns out that the record for Daniel Lopez appears 5 times in the customers database. Basically it is duplicated.

2311 705 Settled 4/15/2005, 5:00 PM 3/31/2005 4/7/2005, 1:00 PM Lopez, Daniel (-6052359) Qurishi, Noreen
2311 705 Settled 4/15/2005, 5:00 PM 3/31/2005 4/7/2005, 1:00 PM Lopez, Daniel (-6052359) Qurishi, Noreen
2311 705 Settled 4/15/2005, 5:00 PM 3/31/2005 4/7/2005, 1:00 PM Lopez, Daniel (-6052359) Qurishi, Noreen
2311 705 Settled 4/15/2005, 5:00 PM 3/31/2005 4/7/2005, 1:00 PM Lopez, Daniel (-6052359) Qurishi, Noreen
2311 705 Settled 4/15/2005, 5:00 PM 3/31/2005 4/7/2005, 1:00 PM Lopez, Daniel (-6052359) Qurishi, Noreen

102 Under Contract 1/30/2005 3/8/2005, 11:30 AM dorthy (-6016111) Linn, Tara
102 Under Contract 1/30/2005 3/8/2005, 11:30 AM buford, dorthy (-6016111) Linn, Tara
103 Settled 4/13/2005, 6:00 PM 4/30/2005 4/6/2005, 4:00 PM Knepp, Randy
104 Settled 4/15/2005, 4:00 PM 4/30/2005 3/8/2005, 1:30 PM paul (-6016170) Linn, Tara

 

[r937]

<SIGH />

okay, just add DISTINCT to your query and see what happens



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[gradinumcp]

You mean this way:-

Well that doesn't do antyhing!

mysql="SELECT DISTINCT * From Project_Units left outer join lcdb.dbo.customers on Project_Units.Contract_Holder=lcdb.dbo.customers.customerid left outer join Project_Buildings on Project_Units.Building_ID=Project_Buildings.Building_ID WHERE Project_Units.ProjectID='"& request.cookies("EFC")("Condo") &"' and unit_number <> '0' order by Building_Number ,floor, Unit_number"

set Cust=cnn.execute(mysql)

 

[r937]

but it would do what you want if in fact you were selecting only the columns that you showed in your sample!

that's part of the problem, isn't it

(hence the <SIGH /> )

you have a one-to-many relationship between your tables (it's hard for me to determine which ones, without seeing the table layouts)

if you select the right columns, you actually can suppress it with DISTINCT -- and also with GROUP BY, provided that you GROUP BY all the non-aggregate columns in the SELECT -- and maybe that's okay after all

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)