Query won't run on local mySQL

Problems with the Windows version of XAMPP, questions, comments, and anything related.

Query won't run on local mySQL

Postby Annaleria » 23. March 2007 16:15

I installed XAMPP to do some work locally instead of being on our remote server as I want to install some new libraries in a test environment first. However my query which works perfectly on our remote server will not work locally.

Here is the query and the result returned when I try to run it through PMA

SQL query: Documentation

SELECT c.property_id AS cid, IF( c.property_id IS NULL , p.property_id, c.property_id ) AS property_id, IF( c.property_name IS NULL , p.property_name, c.property_name ) AS property, pa.property_area_id, pa.area_name, pi.image_path, IF( mpd.short_description IS NULL , ipd.short_description, mpd.short_description ) AS short_description, pf.quantity, MIN( pp.price ) AS price, car_park.feature_type_id AS car_park, car_space.feature_type_id AS car_space, childs_pool.feature_type_id AS childs_pool, private_childs_pool.feature_type_id AS private_childs_pool, complex_pool.feature_type_id AS complex_pool, private_pool.feature_type_id AS private_pool
FROM property_price pp, property_status ps, property p, property_type pt, property_area pa, property_feature pf
LEFT JOIN property c ON ( c.property_id = p.master_property_id )
LEFT JOIN property_image pi ON ( pi.property_id = IF( c.property_id IS NULL , p.property_id, c.property_id )
AND pi.image_type_id =1 )
LEFT JOIN master_property_desc mpd ON ( mpd.property_id = IF( c.property_id IS NULL , p.property_id, c.property_id ) )
LEFT JOIN individual_property_desc ipd ON ( ipd.property_id = p.property_id
AND individual_property_desc_type_id = IF( ps.status_type_id =4, 1, 2 ) )
LEFT JOIN property_feature car_park ON ( car_park.property_id = p.master_property_id
AND car_park.feature_type_id =6
AND car_park.quantity >0 )
LEFT JOIN property_feature car_space ON ( car_space.property_id = p.property_id
AND car_space.feature_type_id =21
AND car_space.quantity >0 )
LEFT JOIN property_feature childs_pool ON ( childs_pool.property_id = p.master_property_id
AND childs_pool.feature_type_id =11
AND childs_pool.quantity >0 )
LEFT JOIN property_feature private_childs_pool ON ( private_childs_pool.property_id = p.property_id
AND private_childs_pool.feature_type_id =11
AND private_childs_pool.quantity >0 )
LEFT JOIN property_feature complex_pool ON ( complex_pool.property_id = p.master_property_id
AND complex_pool.feature_type_id =5
AND complex_pool.quantity >0 )
LEFT JOIN property_feature private_pool ON ( private_pool.property_id = p.property_id
AND private_pool.feature_type_id =5
AND private_pool.quantity >0 )
WHERE pp.price_type_id =1
AND pp.end_date =0
AND pp.price >0
AND ps.property_id = pp.property_id
AND ps.status_type_id
IN ( 4, 5 )
AND ps.end_date =0
AND p.property_id = ps.property_id
AND pt.property_type_id = p.property_type_id
AND pa.property_area_id = p.property_area_id
AND pf.property_id = p.property_id
AND pf.feature_type_id =16
AND pp.property_id = p.property_id
GROUP BY property
ORDER BY price, property
LIMIT 0 , 30

MySQL said: Documentation
#1054 - Unknown column 'p.master_property_id' in 'on clause'

If you check the query you can see that the column does exist, and I have another query

SELECT DISTINCT IF( c.property_id IS NULL , p.property_id, c.property_id ) AS property_id, IF( c.property_name IS NULL , p.property_name, c.property_name ) AS property
FROM property_price pp, property_status ps, property p
LEFT JOIN property c ON ( c.property_id = p.master_property_id )
WHERE pp.price_type_id =1
AND pp.end_date =0
AND pp.price >0
AND ps.property_id = pp.property_id
AND ps.status_type_id
IN ( 4, 5 )
AND ps.end_date =0
AND p.property_id = ps.property_id
ORDER BY property
LIMIT 0 , 30

which works perfectly locally, so I know it isn't the JOIN statement.

Please can someone point me in the right direction as I don't want to give up using XAMPP as my local environment
Annaleria
 
Posts: 2
Joined: 23. March 2007 15:33
Location: UK

Postby Wiedmann » 23. March 2007 17:01

However my query which works perfectly on our remote server will not work locally.

Your remote MySQL server is older then 5.0.12.

Please read:
http://dev.mysql.com/doc/refman/5.0/en/join.html
Wiedmann
AF Moderator
 
Posts: 17102
Joined: 01. February 2004 12:38
Location: Stuttgart / Germany

Postby Annaleria » 23. March 2007 19:43

Thank you for the quick reply and for helping my sanity.

It took me a little while to absorb all the changes, but I must have made sense of it as my query is working again now.

Thanks
Annaleria
 
Posts: 2
Joined: 23. March 2007 15:33
Location: UK


Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 120 guests