CODEWITHSUNDEEP
mysqlsql
sfarzoso
sfarzoso

Reputation: 1600

How to return multiple result in the same query?

I wrote a query that check if a team is updated. The condition to check the update is: if the field update_at of the team is greater than 7 days from the current date, then the record need to be updated, eg:

id | name     | update_at
67   Tirana     2019-03-06 11:00:12  
68   Partizan   2019-03-06 11:02:04  
69   Lusitanos  2019-03-14 09:00:40

SELECT id
FROM team
WHERE update_at < DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
  AND id IN (67, 68, 69);

The query works well, in fact the result above is: 67, 68, because the record 69 is already updated (doesn't fit the condition).

Now suppose that the record that I'm looking for doesn't exist in the database, the query will not return it (and this is good), but how can I check if the record need to be added instead of update? eg:

id | name     | update_at
67   Tirana     2019-03-06 11:00:12  
68   Partizan   2019-03-06 11:02:04  
69   Lusitanos  2019-03-14 09:00:40

SELECT id
FROM team
WHERE update_at < DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
AND id IN (67, 68, 69, 70);

the result is even 67, 68. But contrary to the first example, here the record 70 doesn't exist, so how can I know that using one query?

Is possible return two result such as record_to_update and record_to_add?

Upvotes: 1

Views: 83

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You need a LEFT JOIN using a derived table or similar logic. For instance:

SELECT tt.team_id
FROM (SELECT 67 as team_id UNION ALL SELECT 68 UNION ALL
      SELECT 69 UNION ALL SELECT 70
     ) tt LEFT JOIN
     team t
     on t.id = tt.team_id AND
        update_at >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
WHERE t.id IS NULL;

Here is a db<>fiddle.

EDIT:

If you want to check update versus insert, you can do:

SELECT tt.team_id,
       (CASE WHEN t.id IS NULL THEN 'INSERT'
             ELSE 'UPDATE'
        END) as what_to_do
FROM (SELECT 67 as team_id UNION ALL SELECT 68 UNION ALL
      SELECT 69 UNION ALL SELECT 70
     ) tt LEFT JOIN
     team t
     on t.id = tt.team_id 
WHERE t.id IS NULL OR 
      t.update_at < DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY);

Here is the corresponding db<>fiddle.

By the way, this is an interesting case where LEFT JOIN is used, but the filtering on the second table is in the WHERE clause, not the ON clause.

Upvotes: 2

Ana Iuliana Tuhasu
Ana Iuliana Tuhasu

Reputation: 237

Use another table with IDs and then do a left join between them.

Upvotes: 0

Related Questions