Help with WHERE CLAUSE in a query

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
gastonlm
Forum Commoner
Posts: 26
Joined: Tue May 07, 2002 10:19 am
Location: Buenos Aires, Argentina
Contact:

Help with WHERE CLAUSE in a query

Post by gastonlm »

I need some help with a query. The query does the following:
I'm bringing data from hotels. The hotels have rates. I need to bring the last rates of the hotels and shoe them. For so I'm doing this:

SELECT h.ID,h.name,c.name,p.name ,MAX(ht.dateUntil) FROM hotel AS h, city AS c, country AS p, hotelRate AS ht
WHERE h.namee like '%%' AND ht.hotelID = h.ID AND h.cityID = 13 AND c.grupoID = h.cityID AND c.idiomID = 1 GROUP BY h.name ORDER BY h.name ASC, ht.dateUntil DESC

It works, but I want to bring the rates where the later rates are lower than 2002-12-31. For so I did:

SELECT h.ID,h.name,c.name,p.name ,MAX(ht.dateUntil) FROM hotel AS h, city AS c, country AS p, hotelRate AS ht
WHERE h.namee like '%%' AND ht.hotelID = h.ID AND h.cityID = 13 AND c.grupoID = h.cityID AND c.idiomID = 1 AND MAX(ht.dateUntil) <= '2002-12-31' GROUP BY h.name ORDER BY h.name ASC, ht.dateUntil DESC

but I recived the following error.

MySQL said: Invalid use of group function
Am I clear? Can anybody tell me how should the where clause be?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

You have to alias the max-value and use this alias within the where-clause.
SELECT ....,MAX(ht.dateUntil) as maxuntil ... WHERE ...AND maxuntil <= '2002-12-31' ...
gastonlm
Forum Commoner
Posts: 26
Joined: Tue May 07, 2002 10:19 am
Location: Buenos Aires, Argentina
Contact:

Post by gastonlm »

It should work, bu I tried it and it doesn't work.
I recive the message:
MySQL said: Unknown column 'maxuntil' in 'where clause'
What could be the problem??
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Unfortunately you can't use column alias's in a WHERE clause,
mysql.com docs wrote:It is not allowed to use a column alias in a WHERE clause, because the column value may not yet be determined when the WHERE clause is executed.
Does the query work without the GROUP BY clause?

Mac
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

ahh, merde. You're right. Aliases only working with HAVING
my fault :(
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Haven't used GROUP BY much but would the following work?

Code: Select all

SELECT h.ID,h.name,c.name,p.name ,MAX(ht.dateUntil) FROM hotel AS h, city AS c, country AS p, hotelRate AS ht 
WHERE h.namee like '%%' AND ht.hotelID = h.ID AND h.cityID = 13 AND c.grupoID = h.cityID AND c.idiomID = 1 GROUP BY h.name HAVING MAX(ht.dateUntil) <= '2002-12-31' ORDER BY h.name ASC, ht.dateUntil DESC
Or can you not have a WHERE clause and a HAVING clause in the same SQL statement?

Mac
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

Yep, you can have where clause, group and having clause in 1 query.

One thing I always tell my developers is to start with the basic query and gradually add group by, having etc. checking at each stage they get the results they expect.

Mike
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

gastonlm:
SELECT h.ID,h.name,c.name,p.name ,MAX(ht.dateUntil) FROM hotel AS h, city AS c, country AS p, hotelRate AS ht
WHERE h.namee like '%%' AND ht.hotelID = h.ID AND h.cityID = 13 AND c.grupoID = h.cityID AND c.idiomID = 1 GROUP BY h.name ORDER BY h.name ASC, ht.dateUntil DESC
Having read this query I would doubt that it would work, maybe MySQL is very forgiving when it comes to group by but not including h.ID, c.name and p.name would give an error in Oracle as anything defined in the select must also be included in the Group clause. I don't see your table country being joined to anything in the where clause. Maybe use c.grupoID = 13 rather than h.cityID = 13 might make it slightly quicker as there are fewer records in city than in hotel.
Post Reply