cross checking query against other table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
deejay
Forum Contributor
Posts: 201
Joined: Wed Jan 22, 2003 3:33 am
Location: Cornwall

cross checking query against other table

Post by deejay »

Hi

I have this query that works

Code: Select all

 
$query="SELECT li.rid, li.addone, li.bed, li.tariffCode, li.starRating, li.img1, li.img2 ,li.city , so.percentage, so.start_date, so.end_date, so.status  FROM listings AS li LEFT JOIN special_offers AS so ON li.rid = so.rid WHERE li.llid = $llid AND so.status = 1 AND so.end_date > $date_now ORDER BY rand() LIMIT 4 ";
 
what i need to do though is make a cross check against another table 'weeks' to make sure there is a date entry there.

i thought maybe adding something like

Code: Select all

 
LEFT JOIN weeks AS w ON so.rid = w.rid AND so.start_date <= w.date_vacant AND so.end_date > w.date_vacant
 
or somehow intergrate the code

Code: Select all

so.start_date <= w.date_vacant AND so.end_date > w.date_vacant
to the WHERE statement. But neither seem to work.

Can anyone point me in the right direction.

Thanks
Post Reply