MySQL: linking twice to same table but different rows

Question

I have two tables T1 and T2. There is a 1:n relationship between T1 and T2.

CREATE TABLE T1 (
  PK INT
  /* more columns here */
);

CREATE TABLE T2 (
  PK INT,
  T1_PK INT,
  Active INT,
  Rank INT
  /* more columns here */
);

INSERT INTO T1 VALUES (1);
INSERT INTO T1 VALUES (2);
INSERT INTO T1 VALUES (3);

INSERT INTO T2 VALUES (1, 1, 1, 20);
INSERT INTO T2 VALUES (2, 1, 0, 15);
INSERT INTO T2 VALUES (3, 1, 0, 10);
INSERT INTO T2 VALUES (4, 1, 0, 5);

INSERT INTO T2 VALUES (5, 2, 1, 16);
INSERT INTO T2 VALUES (6, 2, 0, 11);
INSERT INTO T2 VALUES (7, 2, 0, 6);

INSERT INTO T2 VALUES (8, 3, 1, 7);

So each row in T1 has:
– 1 corresponding row in T2 with Active = 1
– 0 to n corresponding rows in T2 with Active = 0

The Rank column in T2 holds values for end-user sorting in the application's UI.

This is the simple query I currently have:

   SELECT T1.PK, T2.Active, T2.Rank
     FROM T1
LEFT JOIN T2 ON T1.PK = T2.T1_PK
    WHERE T2.Active = 1

It returns this result set:

PK      Active  Rank
1       1       20
2       1       16
3       1       7

So that works fine.

Now I would like to get the row values with Active = 0 and MIN(Rank) in the same result set:

PK      Active  Rank    Active  Rank
1       1       20      0       5
2       1       16      0       6
3       1       7       null    null

I played around with a second JOIN, with a subquery, with GROUP BY and HAVING, but to no avail.

Note that the above code pieces can be copied to http://sqlfiddle.com and will work.

As per Gurwinder my question was not a duplicate (at least not to the one linked to). — , May 07 '17 at 19:38

score 0 · Accepted Answer · answered May 07 '17 at 17:25

Try using another left join and this time with an aggregated value for t1_pk with minimum of rank for rows with active = 0.

select t1.pk,
    t2.active,
    t2.rank,
    0 as active_0,
    t.rank as min_rank_0
from t1
left join t2 on t1.pk = t2.t1_pk
    and t2.active = 1
left join (
    select t1_pk,
        min(rank) as rank
    from t2
    where active = 0
    group by t1_pk
    ) t on t1.pk = t.t1_pk;

Demo

MySQL: linking twice to same table but different rows

1 Answers1