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.`D ate 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
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
d.`#Bedrooms`,d.`#Baths`,d
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or you could have used the 'having' reserved word, that allows you to reference alias's in where clauses.
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.`D ate 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
count(*) as num, d.Project, d.`Price Listing`,d.`Address Zip Code`,
(TO_DAYS(NOW()) - TO_DAYS(date_format(str_to
d.`#Bedrooms`,d.`#Baths`,d
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