maybe how to Select from select

Question

Please help, I'm New to mySQL. I'm wondering how I can write a query to produce the below result:

table1

+-----+------+-------+
| id1 |  id2 | col1  |
+-----+------+-------+
| 9   |  1   | foo 1 |
+-----+------+-------+
| 9   |  2   | foo 2 |
+-----+------+-------+
| 9   |  3   | foo 3 |
+-----+------+-------+
| 8   |  4   | foo 4 |
+-----+------+-------+
| 7   |  5   | foo 5 |
+-----+------+-------+

table2

+-----+------+-------+
| id2 | col2 | col3  |
+-----+------+-------+
|  1  | 2018 | bar 1 |
+-----+------+-------+
|  3  | 2018 | bar 2 |
+-----+------+-------+
|  1  | 2017 | bar 3 |
+-----+------+-------+
|  2  | 2017 | bar 4 |
+-----+------+-------+

want to result table1, table2 for id1 = 9 and 2018

+-----+-------+-------+------+--------+
| id1 |  id2  | col1  | col2 |  col3  |
+-----+-------+-------+------+--------+
|  9  |  1    | foo 1 | 2018 | bar 1  |
+-----+-------+-------+------+--------+
|  9  |  2    | foo 2 | 2018 | "NULL" |
+-----+-------+-------+------+--------+
|  9  |  3    | foo 3 | 2018 | bar 2  |
+-----+-------+-------+------+--------+

So far I've tried the following, but haven't gotten it to return the data in the format I'm seeking:

SELECT 
    *
FROM
    table1 a
        LEFT JOIN
    table2 b ON a.id2 = b.id2
WHERE
    a.id1 = 9 AND b.col2 = 2018

thanks and really appreciated.

Answer 1

You were close. Move the year filter into the join condition:

create table table1 (id1 int, id2 int, col1 varchar(20));
insert into table1 (id1, id2, col1) values (9, 1, 'foo 1');
insert into table1 (id1, id2, col1) values (9, 2, 'foo 2');
insert into table1 (id1, id2, col1) values (9, 3, 'foo 3');
insert into table1 (id1, id2, col1) values (8, 4, 'foo 4');
insert into table1 (id1, id2, col1) values (7, 5, 'foo 5');

create table table2 (id2 int, col2 int, col3 varchar(20));
insert into table2 (id2, col2, col3) values (1, 2018, 'bar 1');
insert into table2 (id2, col2, col3) values (3, 2018, 'bar 2');
insert into table2 (id2, col2, col3) values (1, 2017, 'bar 3');
insert into table2 (id2, col2, col3) values (2, 2017, 'bar 4');

SELECT 
    a.id1, a.id2, a.col1, 2018 as col2, b.col3
FROM
    table1 a
    LEFT JOIN table2 b ON a.id2 = b.id2 and b.col2 = 2018
WHERE
    a.id1 = 9

Result:

id1        id2          col1              col2  col3                  
-------------------------------------------------------
9          1            foo 1             2018  bar 1                 
9          2            foo 2             2018  <null>                
9          3            foo 3             2018  bar 2    

Answer 2

It seems the important point in your question is

using LEFT OUTER JOIN with t1.id2 = t2.id2 and t2.col2 = 2018 Join Condition and assigning the current year for non-matching values for the year column as in the following SQL :

select t1.id1, t1.id2, t1.col1, 
       coalesce(t2.col2,year(now())) as col2, 
       t2.col3
  from table1 t1
  left outer join table2 t2 
  on ( t1.id2 = t2.id2 and t2.col2 = 2018 )
 where t1.id1 = 9 
 order by t1.id2;

 id1    id2  col1   col2    col3

  9      1   foo 1  2018    bar 1
  9      2   foo 2  2018    (null)
  9      3   foo 3  2018    bar 2

SQL Fiddle Demo