Page 1 of 1
Query Help
Posted: Thu Oct 26, 2006 8:50 pm
by emmbec
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...
Code: Select all
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.
Thanx!
Posted: Thu Oct 26, 2006 8:54 pm
by feyd
Subqueries weren't supported until MySQL 4.1, so no.
Posted: Thu Oct 26, 2006 9:03 pm
by emmbec
In PHPMYADMIN, on the first page that loads which one is my MySQL version??
LEFT SIDE OF THE PAGE:
RIGHT SIDE OF THE PAGE:
Could you be so kind to tell me which one is my version?
Posted: Thu Oct 26, 2006 9:09 pm
by feyd
Posted: Thu Oct 26, 2006 9:10 pm
by printf
Something like...
Code: Select all
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!
Posted: Thu Oct 26, 2006 9:24 pm
by emmbec
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???
printf wrote:Something like...
Code: Select all
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
Posted: Thu Oct 26, 2006 9:35 pm
by printf
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.
take care
printf