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?
Help with WHERE CLAUSE in a query
Moderator: General Moderators
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
Haven't used GROUP BY much but would the following work?
Or can you not have a WHERE clause and a HAVING clause in the same SQL statement?
Mac
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 DESCMac
gastonlm:
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.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