Can anyone tell me if this is a correct Query syntax for MySQL 4.0??/ I have Xampp on my computer which has MySQL 5.0 and it works great. I don't know what the problem may be...
SELECT p.i_property, p.c_description, (
SELECT count( v.i_property )
FROM visits v
WHERE p.i_property = v.i_property
) 'hits'
FROM properties p
For this query imagine that table PROPERTIES has I_PROPERTY and C_PROPERTY (a description of that property of the property ) two columns only...
Table VISITS only has an ID and I_PROPERTY.
What I'm trying to achieve here is a report of all of my properties and the number of times the've been visited.
SELECT p.i_property, p.c_description, COUNT(v.i_property) AS hits FROM properties AS p LEFT OUTER JOIN visits AS v ON(p.i_property = v.i_property) GROUP BY v.i_property;
Thanx feyd, my hosting service provider told me that I have version 4.1!! Liar!!! I think I will have to switch to another, does anyone knows one that may have an earlier version of MySQL???
SELECT p.i_property, p.c_description, COUNT(v.i_property) AS hits FROM properties AS p LEFT OUTER JOIN visits AS v ON(p.i_property = v.i_property) GROUP BY v.i_property;
printf!
Thanx printf, this solved my problem, but I'm afraid I don't have that much SQL experience as you do, and I don't want to be posting here everytime one of my queries is not supported by MySQL, I guess I will either have to study more SQL or change my hosting =P
It's never bad thing to ask for help, in fact it's the way I myself learned, and many other too! Most anything you can do with a sub-query can be done with a certain join. It's always better to use stuff that has more usability, so join logic should be used first.