Link to home
Start Free TrialLog in
Avatar of andy7789
andy7789

asked on

Complex MySQL query - syntax error

Hi X-perts,

What is wrong with the following query:

SELECT
count(*) as num, d.Project, d.`Price Listing`,d.`Address Zip Code`,
(TO_DAYS(NOW()) - TO_DAYS(date_format(str_to_date(d.`Date Listed`, "%m/%d/%Y"), "%Y-%m-%d"))) as days_from,
 d.`#Bedrooms`,d.`#Baths`,d.`#SQFT Total (numeric)`,d.`Year Built`,
 d.`Address City`,d.`Address State`,d.`MLS Number`,p.latitude, p.longitude,p.id

            FROM idx_5_data d, projects p

            WHERE d.`Address Zip Code`="65049" and

                d.Project = p.project_name  and d.`#Bedrooms` >= "1" and d.`#Bedrooms` <= "6" and d.`#Baths` >= "1"
and (`days_from` <= "77" ) GROUP by d.Project

It returns unknown column days_from, but it was already defined

Please, advise

Thanks

A
SOLUTION
Avatar of Ashish Patel
Ashish Patel
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Or you could have used the 'having' reserved word, that allows you to reference alias's in where clauses.
Avatar of andy7789
andy7789

ASKER

Could you pls explain the syntax of using the 'having' reserved word?
SELECT
count(*) as num, d.Project, d.`Price Listing`,d.`Address Zip Code`,
(TO_DAYS(NOW()) - TO_DAYS(date_format(str_to_date(d.`Date Listed`, "%m/%d/%Y"), "%Y-%m-%d"))) as days_from,
 d.`#Bedrooms`,d.`#Baths`,d.`#SQFT Total (numeric)`,d.`Year Built`,
 d.`Address City`,d.`Address State`,d.`MLS Number`,p.latitude, p.longitude,p.id

            FROM idx_5_data d, projects p

            WHERE d.`Address Zip Code`="65049" and

                d.Project = p.project_name  and d.`#Bedrooms` >= "1" and d.`#Bedrooms` <= "6" and d.`#Baths` >= "1"
GROUP by d.Project
having (`days_from` <= "77" )

I have not tested that but that syntax allows you to reference alias's in the where clause