Page 1 of 1

Help with WHERE CLAUSE in a query

Posted: Mon Jun 10, 2002 11:24 am
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?

Posted: Mon Jun 10, 2002 1:24 pm
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' ...

Posted: Mon Jun 10, 2002 1:40 pm
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??

Posted: Tue Jun 11, 2002 1:48 am
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

Posted: Tue Jun 11, 2002 2:15 am
by volka
ahh, merde. You're right. Aliases only working with HAVING
my fault :(

Posted: Tue Jun 11, 2002 2:19 am
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

Posted: Tue Jun 11, 2002 2:34 am
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

Posted: Tue Jun 11, 2002 2:46 am
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.