I keep getting multiple results when there should only be one, combined result

[Brad Galinson]

I have a long sql query that, among other things, accesses multiple tables with an inner join and some left outer joins. I am expecting it to give a single set of results but when it finds more than one result from the left outer join, it duplicates the results. So, code is basically:

Select tenant.name, r1.roommate, r2.roommate, r3.roommate from Property
Inner Join Tenant on tenant.property = property.property
left outer join room r1 on r1.code = property.code
left outer join room r2 on r2.code = property.code and
r2.code <> r1.code
left outer join room r3 on r3.code = property.code and
r3.code <> r1.code and r3.code <> r2.code

What happens is if there are no rommates, everything works well but if there are any rommates, I get the right query results but it sends the data more than one time instead of just a single result with all the data populated (the results are sent to a work file to print). So, instead of sending something like name, rommate1, roommate2, roommate3 I get name, rommate1 and then name, roommate1, and then name, roommate2, etc so I get 3x results.

Thanks, Brad

[ck9663]

It's because of duplicate records. I assume one if it is because a property could have many tenants, and one property have many rooms.

What output are you expecting?

Happy Coding!!!

~~ CK

[NeoPa]

You have three tables for roommates. This is not a good plan. It's also the fundamental reason why you're multiplying your results.

I would look first at your table structure, and only after that review your SQL.

[Brad Galinson]

No, I have one table with tenants (unique as only one tenant come sup at a time) and on etable with all roommates. a teantn can have one or more roommates. That is why I queried the room table r1,r2,r3 multiple times to get multiple roommates. I expected to get something like Tenant: John Smith; Roommate: Jane Smith; Roommate2: Cindy Smith. I do get those results but it comes out 3x instead of just once.

[NeoPa]

You're absolutely right Brad. I looked for that (I usually use the AS keyword in SQL but I do understand it's optional) but still didn't see it.

Actually, your SQL is a bit disordered. As you know SQL doesn't need to be formatted in any way to be recognised by the SQL engine itself, but that doesn't mean random spacing and case (SQL keywords are typically shown in upper-case) is not important. Reading through SQL in that state is unnecessarily complicated . Tidying it up, I notice there are no parentheses around any of your JOINs. This may not be the problem. I wouldn't expect it to be, but I'm not too sure as I don't know all the hierarchy rules and I never leave such things to chance (Even if I were right in my understanding, the chances are that the next one to come along would not know so would require the parentheses).

See if this makes any difference. I can't see why it wouldn't work, but then I couldn't see why yours didn't either :

SELECT tenant.name
     , r1.roommate
     , r2.roommate
     , r3.roommate
FROM   (((Property
       INNER JOIN
       Tenant
  ON   Property.property = Tenant.property)
       LEFT OUTER JOIN
       room AS r1
  ON   Property.code = r1.code)
       LEFT OUTER JOIN
       room AS r2
  ON   Property.code = r2.code
 AND   Property.code <> r1.code)
       LEFT OUTER JOIN
       room AS r3
  ON   Property.code = r3.code
 AND   Property.code NOT IN(r1.code, r2.code)

If it has the same problem, please post the data that you're using and the results received. Illustrative data is all that is required. The whole dataset is not necessary, but all records for each property that is included, are.