Menju Menju - 3 months ago 19
MySQL Question

MySQL multiple SELECT statements in one query

I'm storing some information in a MySQL table including a date without time.
The date format is a string looking like this: "25.08.2016" (Day.Month.Year).

I want to select the top 50 records from a table descending by a column, but I only want to display the rows with a specific column entry (date).

It is a ranking system and I want to update inactive people.

I would need to combine these 3 queries:

SELECT * FROM `rank` ORDER BY `rank`.`Score` DESC LIMIT 0 , 50;
SELECT * FROM `rank` WHERE NOT (`TimeStamp1` = DATE_FORMAT(NOW(), '%d.%m.%Y') OR `TimeStamp1` = DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 DAY), '%d.%m.%Y'));
UPDATE `rank` SET `inactive` = '1';



  1. Selecting the top 50 people.

  2. Selecting the inactive people of the first query.

  3. Updating the people to inactive.



The most
Score
is rank 1 and that's why I need DESC, I only want to mark the top 50 people as inactive nothing below, below 50 those people are irrelevant that's why I can't use a statement like this:

SELECT * FROM `rank` WHERE NOT (`TimeStamp1` = DATE_FORMAT(NOW(), '%d.%m.%Y') OR `TimeStamp1` = DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 DAY), '%d.%m.%Y')) ORDER BY `rank`.`Score` DESC LIMIT 0 , 50


Yes, it would select 50 rows but not the top 50.

BTW I'm doing it in php.
And I could solve the problem by fetching:

SELECT * FROM `rank` ORDER BY `rank`.`Score` DESC LIMIT 0 , 50;


Then storing the Accound IDs to an array, then query:

SELECT * FROM `rank` WHERE NOT (`TimeStamp1` = DATE_FORMAT(NOW(), '%d.%m.%Y') OR `TimeStamp1` = DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 DAY), '%d.%m.%Y')) ORDER BY `rank`.`Score` DESC LIMIT 0 , 50;


And compare the Accound IDs to the other result, when no match is found I just break the loop.

Can't I just do it with pure MySQL? Can't I query a thing and then filter the results?

Please help me, any more questions?

Answer

Here's my Answer.

UPDATE rank AS target
INNER JOIN (
    SELECT w.id
    FROM rank AS w
    INNER JOIN rank AS e ON e.id = w.id
    WHERE (w.`TimeStamp1` = DATE_FORMAT(NOW(), '%Y-%m-%d') OR w.`TimeStamp1` = DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 DAY),'%Y-%m-%d')) 
    ORDER BY w.`Score` DESC
    LIMIT 50
) AS source ON source.id = target.id
SET inactive = 1;
Comments