Home JOIN from tables evaluating results from one of them
Reply: 2

JOIN from tables evaluating results from one of them

Mazinfriki
1#
Mazinfriki Published in 2018-01-12 22:28:20Z

I have three tables to join, one of them with one-to several values.

SQLFIDDLE

CREATE TABLE Table1  (`id` int, `name` varchar(3));

INSERT INTO Table1   (`id`, `name`)
VALUES   (1, 'A'),  (2, 'B'),   (3, 'C');


CREATE TABLE Table2   (`id` int, `status` int, `date` varchar(9));

INSERT INTO Table2  (`id`, `status`, `date`)
VALUES   (1, 1, '''.11..'''),  (1, 2, '''.12..'''),   (1, 3, '''.13..'''),
         (2, 3, '''.23..'''),  (3, 1, '''.31..'''),   (3, 3, '''.33..''')
;


CREATE TABLE Table3  (`id` int, `value` int);

INSERT INTO Table3   (`id`, `value`)
VALUES     (1, 34),  (2, 22),  (3, 17);

Query 1:

select * from table1

| id | name |
|----|------|
|  1 |    A |
|  2 |    B |
|  3 |    C |

Query 2:

select * from table2;

| id | status |    date |
|----|--------|---------|
|  1 |      1 | '.11..' |
|  1 |      2 | '.12..' |
|  1 |      3 | '.13..' |
|  2 |      3 | '.23..' |
|  3 |      1 | '.31..' |
|  3 |      3 | '.33..' |

Query 3:

select * from table3

| id | value |
|----|-------|
|  1 |    34 |
|  2 |    22 |
|  3 |    17 |

I need query that returns for each id:

   TABLE1.name, TABLE2.status, TABLE2.date, TABLE3.value

with this condition:

  • If TABLE2.status =1 exists then return ONLY that line of TABLE2
  • Else if TABLE2.status =1 does not exists then look for status =2 and return ONLY that line of TABLE2
  • If no one of those values are present in TABLE2 then skip that id from results

EDIT: TABLE2 has an UNIQUE key for id,status so there can be only one id=1 status=1

Thanks for your help!

rlanvin
2#
rlanvin Reply to 2018-01-12 23:10:55Z

Something like this maybe:

select table1.id, table1.name,
    coalesce(table2_status1.status, table2_status2.status) as status,
    coalesce(table2_status1.date, table2_status2.date) as date,
    table3.value
from table1
left join table2 table2_status1 on table2_status1.id = table1.id and table2_status1.status = 1
left join table2 table2_status2 on table2_status2.id = table1.id and table2_status2.status = 2
join table3 on table3.id = table1.id
where (table2_status1.id is not null or table2_status2.id is not null);
Patrick Artner
3#
Patrick Artner Reply to 2018-01-12 23:13:14Z

Not performant, using subselects, works ( but rlanvins https://stackoverflow.com/a/48235077/7505395 is nicer) :

A,B,C instead of First, Second, ...

select 
    TABLE1.name,

    case 
      when exists( select 1 from table2 where id = table1.id and status = 1)
        then 1
      when exists( select 1 from table2 where id = table1.id and status = 2)
        then 2 
    end as T2status,

    case 
      when exists( select 1 from table2 where id = table1.id and status = 1)
        then ( select date from table2 where id = table1.id and status = 1)
      when exists( select 1 from table2 where id = table1.id and status = 2)
        then ( select date from table2 where id = table1.id and status = 2)
    end as T2date,

    TABLE3.value
from table1
join table3 on table1.id = table3.id
where     
    exists( select 1 from table2 where id = table1.id and status = 1)
    or exists( select 1 from table2 where id = table1.id and status = 2)

Output

Name    T2status    T2date  value
A       1           '.11..' 34 
C       1           '.31..' 17

DDL

CREATE TABLE Table1  (`id` int, `name` varchar(3));

INSERT INTO Table1   (`id`, `name`)
VALUES   (1, 'A'),  (2, 'B'),   (3, 'C');


CREATE TABLE Table2   (`id` int, `status` int, `date` varchar(9));

INSERT INTO Table2  (`id`, `status`, `date`)
VALUES   (1, 1, '''.11..'''),  (1, 2, '''.12..'''),   (1, 3, '''.13..'''),
         (2, 3, '''.23..'''),  (3, 1, '''.31..'''),   (3, 3, '''.33..''')
;


CREATE TABLE Table3  (`id` int, `value` int);

INSERT INTO Table3   (`id`, `value`)
VALUES     (1, 34),  (2, 22),  (3, 17);
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.368426 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO