LEFT Join again

furious5

Ok another quick query, I'm trying to join two tables villas, pricing by villa.ID (p key) and pricing.price_id (foreign key). Unfortunately I'm not having much look and I'm getting a syntax error at the JOIN

Table structures look like:

Villa:
ID
Name
Resort

Pricing
ID
price_id
week
availability

SQL looks like this:

SELECT villas.ID, villas.name, villas.resort, pricing.ID, pricing.week, pricing.availability
FROM villas, pricing WHERE villas.resort = 'cala_dor' OR villas.resort = 'pollenca' AND villas.beds LIKE '%' LEFT OUTER JOIN pricing ON (pricing.price_id = villas.ID) AND pricing.week= 1073088000 AND pricing.availability = '1' AND villas.ID = pricing.ID ORDER BY villas.owner DESC, villas.beds ASC

AstroTeg

You're WHERE clause is a little wacky - the JOIN takes place in the FROM clause.

SELECT villas.ID, villas.name, villas.resort, pricing.ID, pricing.week, pricing.availability

FROM villas LEFT OUTER JOIN pricing ON (pricing.price_id = villas.ID)

WHERE villas.resort = 'cala_dor' OR villas.resort = 'pollenca' AND villas.beds LIKE '%' AND pricing.week= 1073088000 AND pricing.availability = '1' ORDER BY villas.owner DESC, villas.beds ASC

villas.beds LIKE '%' - this is a bit of extra work on SQL. You may find leaving this out will help your query.

villas.ID = pricing.ID - this WITH the left outer join doesn't make sense. This chunk of SQL says for every villa ID match it to a pricing ID, but thats not what your LEFT JOIN says. Your LEFT JOIN says for every villa ID, see if there's a pricing ID. If there is, include it, otherwise, just bring back the villa ID. I pulled this out of your where clause. Its legit to be there, but negates what you're trying to do with the LEFT JOIN.

furious5

I've had a bit more of a play around with the code, the OR statement seemed to be causing a few problems so I have put this at the end of the statement and it is now a higher number of records.

My final stumbling block seems that after the join the price and week statements seem to be being ignored.

Can anyone help me out further with this??

SELECT * FROM villas
LEFT OUTER JOIN pricing ON ( pricing.price_id = villas.ID )
WHERE pricing.price > 0 AND pricing.week = 1088809200 AND pricing.availability = 1 AND villas.resort = 'cala_dor' OR villas.resort = 'pollenca'
ORDER BY villas.owner DESC , villas.beds ASC LIMIT 0 , 30

AstroTeg

You try an inner join?