• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

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
0
andy7789
Asked:
andy7789
2 Solutions
 
Ashish PatelCommented:
Try this, and as days_from is dynamic column you will have to use the formula instead of given column name.

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 ((TO_DAYS(NOW()) - TO_DAYS(date_format(str_to_date(d.`Date Listed`, "%m/%d/%Y"), "%Y-%m-%d"))) <= 77 ) GROUP by d.Project
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>It returns unknown column days_from, but it was already defined
alias column cannot be used directly in the query itself.
you will have to repeat the expression:

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 ((TO_DAYS(NOW()) - TO_DAYS(date_format(str_to_date(d.`Date Listed`, "%m/%d/%Y"), "%Y-%m-%d"))) <= 77 ) GROUP by d.Project

please note that I changed also some quotes: for numerical data, do NOT use quotes, that could lead to unexpected results:
"10" < "6"
while
10 > 6


0
 
cg_medistoxCommented:
Or you could have used the 'having' reserved word, that allows you to reference alias's in where clauses.
0
 
andy7789Author Commented:
Could you pls explain the syntax of using the 'having' reserved word?
0
 
cg_medistoxCommented:
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
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now