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

JOIN from tables evaluating results from one of them

user1817
1#
user1817 Published in July 16, 2018, 8:35 pm

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!

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO