Execute a subquery in MySQL that returns multiple rows?

Question

I am executing a query that obviously contains a subquery in MySQL.

Let me just jump into the code:

SELECT DATEDIFF(CURDATE(), 
(SELECT due FROM checkOut JOIN People ON checkOut.p_id = People.p_id
 WHERE CASE WHEN DATE_SUB(date_add(CURDATE(),INTERVAL 4 MONTH), INTERVAL 3 MONTH) 
 >= checkOut.checkTime THEN 1 ELSE 0 END ORDER BY checkOut.due)
 );

The main query is the SELECT DATEDIFF(). Within that is my subquery which essentially searches through the table to look for items that are overdue based on an interval. I know that there will be multiple rows returned from the query and that it will not work with how I currently have it set up.

What I want are multiple values to be returned from my SELECT DATEDIFF(), so that I can loop through it with php later. To elaborate, I want each of the rows returned in the subquery to have an associated value from DATEDIFF(). How can I modify this query to do what I want? Or if anyone has a better method, please let me know.

Any help is appreciated.

In case you are wondering the why there is a DATE_ADD() within the DATE_SUB(), it is to simply make the query work for today.

John Woo · Accepted Answer · 2012-11-05 06:37:02Z

3

get rid of the subquery, you can calculate the difference directly.

SELECT  DATEDIFF(CURDATE(), due), due 
FROM    checkOut JOIN People 
            ON checkOut.p_id = People.p_id
WHERE   CASE 
           WHEN DATE_SUB(date_add(CURDATE(),INTERVAL 4 MONTH), INTERVAL 3 MONTH) 
                    >= checkOut.checkTime 
           THEN 1 
           ELSE 0 
        END 
ORDER BY checkOut.due

answered Nov 5, 2012 at 6:37

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mlagma Over a year ago

That's interesting. Out of curiosity, is there a performance difference between using subqueries or not? It works great by the way.

Smile Over a year ago

That's what I was looking for (y)

Yogendra Singh · Accepted Answer · 2012-11-05 06:37:00Z

0

Use the subquery as table. e.g. below:

SELECT DATEDIFF(CURDATE(), d.due)
FROM 
 (SELECT due FROM checkOut JOIN People ON checkOut.p_id = People.p_id
  WHERE CASE WHEN DATE_SUB(date_add(CURDATE(),INTERVAL 4 MONTH), INTERVAL 3 MONTH) 
       >= checkOut.checkTime THEN 1 ELSE 0 END ORDER BY checkOut.due) 
  ) AS d;

answered Nov 5, 2012 at 6:37

Yogendra Singh

34.5k7 gold badges66 silver badges73 bronze badges

Collectives™ on Stack Overflow

Execute a subquery in MySQL that returns multiple rows?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related