MySQL join, empty rows in junction table

Question

I have three tables I'd like to join in a way that produces all records from one table and any matching records or NULL from another table. It is imperative that all records from the first table be returned. I thought I had done this before but I can't remember when or where and MySQL just isn't playing ball.

SELECT VERSION();
5.0.51a-3ubuntu5.7

/* Some table that may or may not be needed, included to give context */
CREATE TABLE `t1` (
  `a` int(4) NOT NULL,
  `a_name` varchar(10) NOT NULL,
  PRIMARY KEY  (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `t2` ( /* "One table" */
  `b` int(2) NOT NULL,
  `b_name` varchar(10) NOT NULL,
  PRIMARY KEY  (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `t3` ( /* "Another table" */
  `a` int(4) NOT NULL,
  `b` int(2) NOT NULL,
  PRIMARY KEY  (`a`,`b`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO t1 VALUES (1, '1-one'),(2, '1-two'),(3, '1-three');
INSERT INTO t2 VALUES (1, '2-one'),(2, '2-two'),(3, '2-three'),
                      (4, '2-four'),(5, '2-five');
INSERT INTO t3 VALUES (1,1),(1,2),(1,3),(1,4),(2,2),(2,5);

t3 is a junction table for t1 and t2. The result set I'm looking for should look like this for any a=n:

n=1
b | b_name  | a
-------------------
1 | 2-one   | 1
2 | 2-two   | 1
3 | 2-three | 1
4 | 2-four  | 1
5 | 2-five  | NULL

n=2
b | b_name  | a
-------------------
1 | 2-one   | NULL
2 | 2-two   | 2
3 | 2-three | NULL
4 | 2-four  | NULL
5 | 2-five  | 2    

n=7
b | b_name  | a
-------------------
1 | 2-one   | NULL
2 | 2-two   | NULL
3 | 2-three | NULL
4 | 2-four  | NULL
5 | 2-five  | NULL

The value of a in the result set actually isn't important as long as it unambiguously reflects the presence or absence of records in t3 (does that make sense?).

The query

SELECT t2.b, t2.b_name, a
   FROM  t2
   LEFT /* OUTER */ JOIN t3 ON t3.b = t2.b
   WHERE (
     a = 2
     OR
     a IS NULL
     );

returns

b | b_name  | a
-------------------
2 | 2-two   | 2
5 | 2-five  | 2    

Can this be done?

Answer 1

Something like this? If not, can you give an example of the output you'd like to get.

select * from t1
left join t3 using(a)
left join t2 using(b)

Answer 2

SELECT t2.b, t2.b_name, MAX(IF(a=2, a, NULL)) AS a
FROM t2
     LEFT OUTER JOIN t3
          ON t3.b = t2.b
GROUP by t2.b
ORDER BY b ASC;