repeating left join

serichens

I'm trying to use a left join to list all the records in multiple tables, but it is repeating. (It goes back and lists the dealer table again)

I have 3 tables:

dealers
dealerId, dealerName

address2Rel
address2RelId, reldealerId, reladdress2Id

addresses2
addresses2Id, address2

I'm using the statement

SELECT dealerName, address2 FROM dealers, address2Rel LEFT JOIN addresses2 ON addresses2.addresses2Id = reladdress2Id AND address2Rel.reldealerId = dealers.dealerId WHERE address2Rel.reldealerId = dealers.dealerId

I need to list all the dealerName(s), even if this dealer does not have an address2. Whenever I put a where clause, I only get the dealers that have an address2.

How can I get it to just go through once?
thanks

tomhath

I don't quite follow what you said, but here's how I'd write it:

SELECT dealerName, address2
FROM dealers
INNER JOIN address2Rel
ON address2Rel.reldealerId = dealers.dealerId
LEFT JOIN addresses2
ON addresses2.addresses2Id = reladdress2Id

serichens

I tried that, but that gives only the 2 dealers that have an address2.

What I mean is, there are 174 dealers in the table. Only 2 have a second address (address2). When I use the select statement shown in my original post, I get 348 records returned. I want to list all the dealers, with AND without a second address. I expect 174 records, 2 with an address2, and 172 with a null entry for address2.

I tried DISTINCTROW, but that was kind of unpredictable. In phpMyAdmin, it showed the results I wanted, but it didn't recognize them as a record number. (I couldn't use the 'show 30 row(s), starting from record # boxes. It gave an error saying the record number was out of range). And I couldn't get DISTINCTROW to work in the php script.

thanks

tomhath

Try making them both LEFT JOINs:

SELECT dealerName, address2
FROM dealers
LEFT JOIN address2Rel
ON address2Rel.reldealerId = dealers.dealerId
LEFT JOIN addresses2
ON addresses2.addresses2Id = address2Rel.reladdress2Id

serichens

that's it!!

thanks!