How do I do a MySQL select and Join Query with differing row counts and perform a calculation on two-CodePudding

I have two tables, the first one has 20 rows. I can select the data from that easy enough. returning 1 column and 20 rows. returned ex. column name - "Widget ID" ex. Row "10"

In the second table could have any number of rows, I want to check if that previously selected column data "Widget ID" matches. If it does match I want to check another column in the second table to see if it is "NULL" then return a TRUE if it is NULL and FALSE if it isn't.

How would I do this for every row returned from the first select statement and join it.

My return I am looking for is Column1 - "Widget ID" Column2 - "Widget Status" True/False and to have the same number of rows as the first table containing all of the "Widget ID"

Is this do-able?

Sample Data From First Table

 ----------- 
| widget_id |
 ----------- 
|         1 |
|         2 |
|         3 |
|         4 |
|         5 |
|         6 |
 -----------

Sample Data From Second Table

 ---- --------------------- 
| id | date                |
 ---- --------------------- 
|  1 | 2022-02-16 10:52:44 |
|  3 | 2022-02-16 10:52:44 |
|  2 | NULL                |
|  4 | NULL                |
 ---- ---------------------

Results I am trying to get - Second column using the BOOL data type - If ID doesn't exist in second table assume FALSE I am not trying to create a table, this is just the result i am trying to get back

 ---- --------------------- 
| id | status              |
 ---- --------------------- 
|  1 | FALSE               |
|  2 | TRUE                |
|  3 | FALSE               |
|  4 | TRUE                |
|  5 | FALSE               |
|  6 | FALSE               |
 ---- ---------------------

CodePudding user response：

Assuming you want false when no result is found in the second table you could try something like

SELECT 
    `table_1`.`id`,
    CASE WHEN table_2.column_to_check = 'true' 
    THEN 'true' 
    ELSE 'false' 
    END as `status`
FROM
    `table_1` LEFT JOIN `table_2` ON `table_1`.`id` = `table_2`.`table_1_id`
;

Take a look here for an example