CODEWITHSUNDEEP
sqloracle-databasejoin
John Doe
John Doe

Reputation: 3

How to join tables to get correct result?

I have two tables:

Things:

+----+---------+--+
| id |  type   |  |
+----+---------+--+
|  1 | issue   |  |
|  2 | issue   |  |
|  3 | issue   |  |
|  4 | issue   |  |
|  5 | issue   |  |
|  6 | bug     |  |
|  7 | bug     |  |
|  8 | bug     |  |
|  9 | bug     |  |
| 10 | bug     |  |
| 11 | project |  |
| 12 | project |  |
+----+---------+--+

Links:

+----+--------+-------------+-----------+
| id | source | destination | link_type |
+----+--------+-------------+-----------+
|  1 |      1 |           6 | i_b       |
|  2 |      2 |           7 | i_b       |
|  3 |      3 |           8 | i_b       |
|  4 |      1 |          10 | i_p       |
|  5 |      2 |          11 | i_p       |
|  6 |      3 |          10 | i_p       |
|  7 |      4 |          11 | i_p       |
|  8 |      5 |          10 | i_p       |
+----+--------+-------------+-----------+

I would like to list all things of type issues in the first column and all connected bugs to them in the second column (link_type = i_b). If there is no connected bug there should be NULL:

+----------+--------+
| issue_id | bug_id |
+----------+--------+
|        1 | 6      |
|        2 | 7      |
|        3 | 8      |
|        4 | NULL   |
|        5 | NULL   |
+----------+--------+

I tried to do it with joins, but cannot get rows with issues without bugs connected:

select issue.id as issue, bug.id
from things issue
    left join links
        on issue.id = links.source
    join things bug
        on links.destination = bug.id

which results:

+-------+-----+
| issue | bug |
+-------+-----+
|     1 |   6 |
|     2 |   7 |
|     3 |   8 |
+-------+-----+

with missing issues 4 and 5.

Upvotes: 0

Views: 71

Answers (5)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Select issues from Things (with type = 'issue'). Outer join Links that link a bug to an issue (i.e. link_type = 'i_b').

select
  t.id as issue_id,
  l.destination as bug_id
from things t
left join links l on l.source = t.id and l.link_type = 'i_b'
where t.type = 'issue';

(You could add an outer join to 'bug' Things records, but you'd gain nothing. Except maybe for some insurance against invalid Links entries. The database cannot really guarantee data integrity - e.g. that an 'i_b' record really contains an issue ID and a bug ID - because you chose a very generic design for some reasons that has this drawback.)

Upvotes: 0

user5683823
user5683823

Reputation:

Checking if the source is an 'issue' requires only a semi-join (an IN condition, less expensive than a complete join). You also need to differentiate between rows with an issue id and a non-bug destination, depending on what other rows may exist where the destination is a bug. This requires more than just joins. I solve that with a call to the analytic function count() in a subquery.

with things ( id, type ) as (
       select  1, 'issue'   from dual union all
       select  2, 'issue'   from dual union all     
       select  3, 'issue'   from dual union all
       select  4, 'issue'   from dual union all
       select  5, 'issue'   from dual union all
       select  6, 'bug'     from dual union all
       select  7, 'bug'     from dual union all
       select  8, 'bug'     from dual union all
       select  9, 'bug'     from dual union all
       select 10, 'bug'     from dual union all
       select 11, 'project' from dual union all
       select 12, 'project' from dual
     ),
     links(  id, source, destination, link_type ) as (
       select 1, 1,  6, 'i_b' from dual union all
       select 2, 2,  7, 'i_b' from dual union all
       select 3, 3,  8, 'i_b' from dual union all
       select 4, 1, 10, 'i_p' from dual union all
       select 5, 2, 11, 'i_p' from dual union all
       select 6, 3, 10, 'i_p' from dual union all
       select 7, 4, 11, 'i_p' from dual union all
       select 8, 5, 10, 'i_p' from dual
     )
-- end of test data (NOT part of the solution); query begins BELOW THIS LINE
select issue, bug
from ( select l.source as issue,
              case when t.type = 'bug' then l.destination end as bug, 
              count(case when t.type = 'bug' then 1 end) 
                                 over (partition by l.source) as ct
       from   links l inner join things t on l.destination = t.id
       where  l.source in (select id from things where type = 'issue')
     )
where bug is not null or ct = 0
;

Output:

ISSUE BUG
----- ---
    1   6
    1  10
    2   7
    3   8
    3  10
    4
    5  10

7 rows selected.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You only need one join, if we assume (as in your sample data) that all issues are in links. Also, I think you want to check of "bug" in the type column:

select l.source as issue, bug.id
from links l left join
     things bug
     on bug.id = l.destination and bug.type = 'bug';

If this is assumption is not correct, you can use issues with two left joins:

select l.source as issue, bug.id
from issues i left join
     links l
     on i.id = l.source left join
     things bug
     on bug.id = l.destination and bug.type = 'bug';

Upvotes: 0

Tajinder
Tajinder

Reputation: 2338

Hoping, i understood correctly. Please check below query.

select issue.id as issue, bug.id from things  
left outer join links
on issue.id = links.destination 
where a.type = 'issue';

Upvotes: 0

MikeS
MikeS

Reputation: 1764

I think you need the second join to be a left join:

select issue.id as issue, bug.id
from things issue
    left join links
        on issue.id = links.source
    left join things bug
        on links.destination = bug.id

Upvotes: 1

Related Questions