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 :wink: ) 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:

Code: Select all

# Server version: 4.0.27-standard
RIGHT SIDE OF THE PAGE:

Code: Select all

# MySQL client version: 4.1.10
Could you be so kind to tell me which one is my version?

Posted: Thu Oct 26, 2006 9:09 pm
by feyd
run

Code: Select all

SELECT VERSION();

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
feyd wrote:run

Code: Select all

SELECT VERSION();
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