Query Help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
emmbec
Forum Contributor
Posts: 112
Joined: Thu Sep 21, 2006 12:19 pm
Location: Queretaro, Mexico

Query Help

Post 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!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Subqueries weren't supported until MySQL 4.1, so no.
User avatar
emmbec
Forum Contributor
Posts: 112
Joined: Thu Sep 21, 2006 12:19 pm
Location: Queretaro, Mexico

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

run

Code: Select all

SELECT VERSION();
printf
Forum Contributor
Posts: 173
Joined: Wed Jan 12, 2005 5:24 pm

Post 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!
User avatar
emmbec
Forum Contributor
Posts: 112
Joined: Thu Sep 21, 2006 12:19 pm
Location: Queretaro, Mexico

Post 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
printf
Forum Contributor
Posts: 173
Joined: Wed Jan 12, 2005 5:24 pm

Post 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
Post Reply