MySQL query, not able to get correct result with Where, Group by and having

Try this.

SELECT  `orders_id` 
FROM  `orders_status_history` 
GROUP BY  `orders_id`
HAVING MIN(`orders_status_id`) = 1 AND MAX(`orders_status_id`) = 1
ORDER BY `orders_id`

Select allOpen in new window

Question: Why would you have more than one record for the same order? Surely if your orders are going throug the process you are only going to have one record for each order.

Your sample data does not show duplicates but your post and query seem to indicate that they are.

Assuming there are then this will do what you want

SELECT  `orders_id`
FROM  `orders_status_history`
WHERE status = 1
GROUP BY  `orders_id`
HAVING COUNT(`orders_status_id`) = 1
ORDER BY `orders_id`

Select allOpen in new window

Hi ,

What is the problem with your sql ? For me it looks correct .

Thx

Hi everyone,
Thanks for the responses.

@Magento,
I wanted to confirm if I've written it correctly so that I'm getting the correct result.

@ Sharath_123,
It gives me different result from my query, I'm more confused, which one is correct?

@julianH,
It's not correct since WHERE clause in your query makes the Grouping ineffective.

Regards

I want to list all the orders where there is only one status which is Pending (1)

Can't see how you can get away without a filter - the where clause is necesary.

Your query is not going to work because nowhere does it actually check the value of the status and you specifically only want status=1 if I read your question correctly

What I don't understand is why you are using group and count?

If all you want is all orders that have a status of 1 - unless you are creating duplicate records with a status of 1 then there is no need to do the group - and even if there are duplicates you are saying you are only interested in records that have only 1 status pending - which does not make sense.

Why does this not give you what you want

SELECT  `orders_id`
FROM  `orders_status_history`
WHERE orders_status_id=1
ORDER BY `orders_id`

Select allOpen in new window

If this is not correct then please say from your sample data what you expect the results to look like.

Ok I understand what you want now - you want only those orders that have a single status record and it must be pending ...

Here is what you want

SELECT orders_status_history_id, orders_id 
FROM orders_status_history 
WHERE orders_status_id = 1 AND orders_id IN
(
	SELECT orders_id FROM orders_status_history GROUP BY orders_id HAVING COUNT(*) = 1
)

Select allOpen in new window

The subquery will return all orders that have only one record - the outer query will filter those to be only those with a status of 1.

@ Sharath_123,
It gives me different result from my query, I'm more confused, which one is correct?

The query I posted should give exactly what you are looking for. Verify the result of that query if it is matching with your expected result. If you have problem, post here, will look into that.

@Sharath_123 - question - how is your query going to ensure only records with status 1 are returned - you have no filter for orders_status_id other than a min and max which only checks for instances not values?

@practitioner - please try the query I posted above and let me know if that works. I have tested against a mockup of your database and it appears to work as expected.

Alright guys,
I'm back, will check it today and get back.
Thanks

julianH - If you have time, you can test and let me know if it is not working. Thanks.

@julianH, it did not return any result as this always gets stuck, I guess I need to run it on a test database with less number of records.

Thanks Julian,
I really appreciate your kind support. I wanted to list all these orders to make sure that I'm updating the correct ones. So instead of using a query and update them in one shot, I wrote a small PHP code, added it to the CRON so that it can update a few records every time the cron runs, the cron runs every 5th minute and it took almost 24 hours to fix this problem on my database.

Now, how do i assign the points and accept the solution so that if someone with similar requirement comes on EE knows that this is the answer :-)

Regards

Which answer do you want to accept?

Your original question was

So I wrote this query but I'm not sure how correct it is:

The answer to your question is what I wrote in my last post - what you had is correct.

Hi Moderator,
How do I accept my own post as the best solution and award points to the experts who helped me?

@practitioner close the question as you normally would - accept your own answer with 0 points and assign points to other questions as per normal.

Alright, so the answer is my own query as approved by Julian:

 

SELECT  `orders_id`
FROM  `orders_status_history`
GROUP BY  `orders_id`
HAVING COUNT(`orders_status_id`) = 1
ORDER BY `orders_id`

I think the answer's intention was to accept own answer 0 points and this answer as assisted a39451928

You are absolutely right Julian, I messed up in hurry, thanks for raising the objection.

NP :)

Thanks Moderator.
@Julian, although I fixed my issue by writing a small PHP code and looped through to save time, I checked once again my original query and figured out the the part which is missing in it and why I was not sure at the time of posting this, yes, the missing part in my query is filtering the results further by checking if the orders_status_id value is 1 which is pending and not any other, the other values are from 2 to 8 that denotes different status of an order e.g. shipped, processed etc.

In fact, you tried to filter this in

Your suggestion and I tried it today again with no luck, so we are again in the discussion mode and can't close it as it will be misleading solution :-)

I apologize for the inconvenience but glad that it was not closed with wrong solution.

Try this one

SELECT orders_status_history_id, orders_id 
FROM orders_status_history 
WHERE orders_status_id = 1 AND orders_id IN
(
	SELECT orders_id FROM orders_status_history GROUP BY orders_id HAVING COUNT(*) = 1
)

Select allOpen in new window

Any update on my last post?

Hey Julian,
Sorry dear, I could not check it.
I tried it today and the MySQL server stopped responding, PHPMyAdmin says "lost connection to MySQL"

@SouthMod, I understand that a timely response is expected from the one who posted the question, I'll respond on time.

At this point the asker was prepared to accept this solution