[SOLVED] Correct syntax to join 3 tables

[poleposters]

Hi

 

I have three tables. One has the user information, one their address and the other some listing information.

 

I want to join them. I tried doing something similar to joining 2 tables but thats not working. Can anyone tell me what I'm doing wrong?

 

 $selectlinks="SELECT * from coupon,users,address  WHERE coupon.business_id=users.business_id=address.business_id AND postcode like '%$searchterm' and cat_id=$catid or coupon.business_id=users.business_id=address.business_id AND suburb like '%$searchterm' and cat_id=$catid order by paid_listing DESC limit $start,$numentries";

[zenag]

use left join ...

select table1.fieldname,table2.fieldname,table3.fieldname.... from table leftjoin table2 on table1.fieldname=table2,fieldname leftjoin table3  on table2.fieldname=table3.fieldname where  any condition...

[poleposters]

Great!

 

Except now I'm running into some trouble with a similar query.

 

$selectlinks="SELECT * FROM coupon LEFT JOIN users ON coupon.business_id=users.business_id LEFT JOIN address  ON users.business_id=address.business_id  WHERE business_id=$businessid order by date_added DESC limit $start,$numentries";

 

I get this message

 

Column 'business_id' in where clause is ambiguous

 

 

[zenag]

where should be specified as

WHERE tablename.business_id=$businessid 

[poleposters]

Brilliant!

 

Thanks so much