CODEWITHSUNDEEP
mysqlsqllogic
Burning Hippo
Burning Hippo

Reputation: 805

LEFT JOIN with 3 tables

I am having trouble joining 3 tables together. I have a SELECT that works for two of the tables, which is the main data i'd like to see. I would also like to add the rate and tax to the units that are selected. They are stored in a different table because units can have more than one rate in my situation. Here is the query that works:

SELECT u.unit_name from units u 
LEFT JOIN reservations r ON r.unit = u.id
    AND r.arrival <= 2013-10-11
    AND r.departure >= 2013-10-01
WHERE r.unit IS NULL

And the one I am trying to make that doesn't:

SELECT u.unit_name, ur.unit, ur.rate, ur.tax
FROM units u
LEFT JOIN reservations r 
INNER JOIN unit_rates ur 
    ON r.unit = u.id
    ON ur.unit = u.id
    AND r.arrival <= 2013-10-11
    AND r.departure >= 2013-10-01
WHERE r.unit IS NULL 
    AND ur.active = TRUE

HERE is the SQLfiddle: http://sqlfiddle.com/#!2/b303ad

Upvotes: 1

Views: 102

Answers (2)

Mihai
Mihai

Reputation: 26804

SELECT u.unit_name, ur.unit, ur.rate, ur.tax 
FROM units u 
LEFT JOIN reservations r ON r.unit = u.id
AND r.arrival <= 2013-10-11
AND r.departure >= 2013-10-01
JOIN unit_rates ur ON ur.unit = u.id
WHERE r.unit IS NULL

Put the ON condition after each JOIN.

SQL fiddle

Upvotes: 2

Rogue
Rogue

Reputation: 11483

Just use AND within your ON:

SELECT u.unit_name, ur.unit, ur.rate, ur.tax
FROM units u
LEFT JOIN reservations r
INNER JOIN unit_rates ur 
/* Another table */
ON r.unit = u.id
    AND ur.unit = u.id
    AND r.arrival <= 2013-10-11
    AND r.departure >= 2013-10-01
WHERE r.unit IS NULL AND ur.active = TRUE

Upvotes: 0

Related Questions