Duane Lortie Duane Lortie - 2 months ago 19
MySQL Question

Adding AS table.column to LEFT JOIN query

I have 2 tables "data" and "listings" that I'm doing a LEFT join on. Both tables have an ID column. The problem is, I can't figure out how to do AS with a join so I can later reference data.ID and listings.ID in my PHP script. Here's my current query, which results in 2 columns both labeled ID

`SELECT * FROM `listings` LEFT JOIN `data` ON ( `listings`.`REALTOR` = `data`.`ID` ) WHERE `listings`.`ACTIVE` = '1' AND `listings`.`MODIFIED` < NOW( ) - INTERVAL 90 DAY `

Answer
SELECT l.*, d.*,
       l.id as listing_id, 
       d.id as data_id
FROM listings l
LEFT JOIN data d ON l.REALTOR = d.ID
WHERE l.ACTIVE = '1' 
AND l.MODIFIED < NOW() - INTERVAL 90 DAY
Comments