how sql join works on 3 tables with example

Question

I am new to sql joins. can anyone explain me with a simple example how left/right/outer joins work with respect to joining 3 tables?

say i have the following tables Area

Area ID PersonID
1        11
2        12
3        13

Disease

DiseaseID   Disease Name
4            ABC
5            DEF

Attack

AttackID    Disease ID  AreaID
111            4          1
222            4          2
222            5          1

I want to know the count of people who were attacked and who were not attacked by disease.

Start here: http://stackoverflow.com/questions/9853586/sql-join-multiple-tables — shree.pat18, Jul 07 '15 at 09:52
That's only 2 tables. Joins of 3 tables work exactly the same as joins of 2 tables. — Barmar, Jul 07 '15 at 09:53
Refer here -> http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins/28598795#28598795 — Pratik Joshi, Jul 07 '15 at 09:53
I would suggest, first start reading the [Database SQL Language Reference](http://docs.oracle.com/database/121/SQLRF/toc.htm). — Lalit Kumar B, Jul 07 '15 at 10:02

score 0 · Answer 1 · edited Jul 07 '15 at 11:41

I hope it will full fill your requirement

SELECT R.AREA_ID,
       COUNT(A.DISEASEID) ATTACKED_PEOPLE
  FROM AREA R
  LEFT JOIN ATTACK A
    ON A.AREAID=R.AREA_ID
  LEFT JOIN DISEASE D
    ON D.SISEASE_ID=A.DISEASEID
  GROUP BY R.AREA_ID;

Here we have used joining of three tables using LEFT JOIN concept. So the left side table (AREA) retrieves all the records and the right side table (ATTACK) retrieves only matched records with left side table. As well as second LEFT JOIN condition is for retrieving the count of diseases from ATTACK table. So that we can get count of people who were attacked and who were not using the above query.

score 0 · Answer 2 · answered Jul 07 '15 at 13:03

Only tables AREA and ATTACK contains informations you need here (you don't want disease name), so at first attach informations about attacks to table containing person ID:

select personid, count(diseaseid) cnt
  from area left join attack using (areaid)
  group by personid

PersonID  Cnt
      11    2
      13    0
      12    1

Now you can count ill and healthy persons:

select 
    count(case when cnt > 0 then 1 end) Affected,
    count(case when cnt = 0 then 1 end) NotAffected
  from (
    select personid, count(diseaseid) cnt
      from area left join attack using (areaid)
      group by personid)

Affected  NotAffected
       2            1

^{SQLFiddle demo}

There are several ways to get last output, try to find others. And example how to join all three tables:

select personid, count(diseaseid) cnt, 
    listagg(DiseaseName, ', ') within group (order by DiseaseName) Diseases
  from area 
  left join attack using (areaid)
  left join disease using (diseaseid)
  group by personid

PERSONID   CNT  DISEASES
--------  ----  ----------
      11     2  ABC, DEF
      12     1  ABC
      13     0

If you want to find only affected persons use INNER JOIN instead of LEFT.

how sql join works on 3 tables with example

2 Answers2