need help in a simple query

Matth_S

Hello,

i'm trying to write a query that seems rather simple...

So, considering the 3 important fields in my query :
table1(id_table1,id_table2,event).
This table is linked to another table with a 1-n linkage.
So, different entries in the table1 can have the same id_table2, but different id_table1 and event.

There's an order for those events (their value can be from 1 to 5).
I mean, if there is a row in the table with the value 3 in the event field, there must be at least 2 other rows sharing the same id_table2 with the value 1 and 2 in the event field.

I want to get for example the rows of which event value is 2, and for which there are no result sharing the same id_table2 with higher event value. (really difficult to explain this problem I don't know if it's clear).

but for example :
(id_table1,id_table2,event)
(50,50,1)
(51,50,2)
(52,60,1)
(53,60,2)
(54,60,3)

---> I want to get only the 2nd row : (51,50,2)

I'm really unable to write this query !

Thanks

paulnaj

Not sure I completely understand you, but this might be what you're looking for ...

SELECT 		id_table1
,		id_table2
,		MAX(event)
FROM		table1
GROUP BY	id_table2

If it's not correct, try and tell us what's wrong with it!

Paul 🙂

Matth_S

Thanks for your answer !

I think in the query you suggested, I'll only get the Max event value for each id_table2 ; but I want to get the result matching a specified value of the event field.

Moreover, It's difficult to use Max() function, actually the field event is a VARCHAR one, and the actual value of "event" can be 'rappel 1', 'rappel 2' and so...I just simplified it in my previous post. But, anyway, I can easily alter the table and change the field into a integer if the use of a Max function is needed and allows me to get the result as previously described.

paulnaj

Hi Matt,

Hmm, still don't get what is it you actually want, so let's start again ...

SELECT id_table1
, id_table2
, event
FROM table1
WHERE event = 'rappel 1'

... will give you all records with event 'rappel 1'.

Can you try and describe again which of these records you want returned?

Paul 🙂

Matth_S

Thanks again for your answer.

With the query you suggested, I'll get the rows of which the event value is 'Rappel 1' ; but the problem is, I don't want to get the rows in which event is 'Rappel 1' if there are also in the table records with the same 'id_table2' but a higher value of event.

In fact, if there is a 'Rappel 2' event in a row, this row must have an 'id_table2' value for which there is also a 'Rappel 1' event in the table. I can't write in the table the value 'Rappel 2' if there is no 'Rappel1' for the corresponding id_table2.

I don't know if it's clear, it's not easy to explain.

paulnaj

Eurghh! That's horrible. Not sure if ypu're gonna be able to do what you want in one query but try this ...

SELECT 		table1.id_table1 
, 		table1.id_table2 
, 		table1.event 
FROM 		table1 
LEFT JOIN 	table1 AS `table1_alias` USING (id_table2)
WHERE		table1.event = 'rappel 1' 
AND (		table1_alias.event < table1.event OR table1_alias.event IS NULL)

I am assuming that the events can still be ordered (that is 'rappel 1' comes before 'rappel 2', etc.)

Paul 🙂

Paul.

Matth_S

Thanks !

I just tried the query you suggested, but I still don't get the wanted results :
For example, if I try to select the rows with 'rappel1' as event, I don't get any results (I assume it's because 'rappel1' is the first event, and so there is only 1 record in the table with 'rappel1' if no 'rappel2' event has occured for the same id_table2). If I try to select the rows with 'rappel2' as event, I get each records with 'rappel2', even if those records have an id_table2 in other rows with a higher event level. If I try 'rappel3' (no row in my table has a higher event level), I get twice the same result for the rows of which the event value is 'rappel3'.

But, If you think I won't manage to get this in only 1 query, I'll try a different way. Maybe I can sort this out with 2 or 3 queries using php code to sort the matching results ; but I wanted to try a better way of programming first (I mean, 2 or 3 queries needing php code to sort the results isn't a proper way of programming...).

Btw, I've already seen queries such as the one you suggested ; it's the way to replace subqueries in mysql ? I definitely need to improve my level in mysql !

Thanks again for your help !

paulnaj

Hi Matt,

Not gonna give up!!

Had a thought ... if event was just a number, this should work ... it might still as it stands (if the text in event can be ordered successfully) ...

SELECT		id_table1
,		id_table2
,		MAX(event) as max_event
FROM		table1
GROUP BY	id_table2
HAVING 		max_event = 'rappel 1' /* or whatever */

Paul 🙂

PS. Using joins is not really a replacement for subqueries ... although there are times when it is. Without subqueries, you often have to use more than one query but I agree, the less queries, the better.

Matth_S

It works perfectly !

Thanks for your help !

Matth_S

I still need help 🙁

I tried to complicate a little the query you suggested. In the same table1 is also stored the date of the event.

When I select the date of the event, I don't get the row's date of the event which is the MAX. If I check in which row is stored the date I've gotten, it appears to be stored in another row, with the same id_table1, but not the MAX(event) as event.

for example, if I have 2 rows (considering the fields (id_table2,event,date)):
(65,'rappel1','2005-06-05')
(65,'rappel2','2005-06-08')

With this query, selecting the date, I'll get :
'rappel2' (as it's the MAX(event) for this id_table2), but the date is 2005-06-05.

I don't know how mysql sorts a query with a having statement, so I don't understand if such a result is normal.

Another question :

I'd like to add conditions in the having clause, is it possible to add a condition such as :
HAVING MAX(event)='rappel2' AND UNIX_TIMESTAMP(date)<=(UNIX_TIMESTAMP(NOW())-36002430)
(In case of a 'rappel2' event, I want to select this event only if it occured 30 days ago)

paulnaj

Hmmm,

I would have expected the correct date to appear. I'll have to have a think about that.

For your second question, yes you can have multiple conditions in the HAVING clause, but it doesn't apply in this instance.

The reason is this: GROUP BY and HAVING will only work with aggregate columns (in our case that's only MAX(event)). All other columns are not aggregate, so constraints on them should be in a WHERE clause.

SELECT		id_table1
,		id_table2
,		MAX(event)
FROM		table1
WHERE 		UNIX_TIMESTAMP(date) <= UNIX_TIMESTAMP(NOW())-3600*24*30
GROUP BY	id_table2
HAVING 		MAX(event) = 'rappel2'

Paul.

Matth_S

Thanks for your answer !

Actually, I tried to put the date conditions in the WHERE clause, but it seems that first the rows matching those date conditions are selected and then the GROUP BY conditions is applied. Which means, if there's a row with a date that doesn't match the date conditions, but the event of which is 'rappel3' ; and there's another row, sharing the same id_table2 but with a date that fits ; I'll get this row despite the MAX(event) for this id_table2 is 'rappel3'. Cause, as it doesn't fit the date conditions, the row with 'rappel3' has been set aside, and the MAX(event) for the remaining rows is 'rappel2'.

Another question (which may help me to undertand the results I get). How mysql sorts the result if an aggregate function and a group by clause are used ? I mean, only one row is return for each field used in the group by condition. But, how the other fields are selected ? For example, if I've 3 rows (considering the 4 fields of my table : id_table1,id_table2,event,date)) :
(1,65,'rappel1','2005-06-05')
(2,65,'rappel2','2005-06-08')
(3,65,'rappel3','2005-06-09')

With a query such as
SELECT id_table1,id_table2,MAX(event),date
FROM table1
GROUP BY id_table2

The group by condition will make mysql return only 1 row, but which value for id_table1 and date is selected ?