Join multiple SQL tables

Question

Not working

SELECT a.name, atn.name
FROM t1 a
    JOIN t2 ap ON a.id = ap.area_id
    JOIN t3 atn ON atn.id = ap.parent_id

I have a table t1 with area names and their type (like pin, ward and simple area name) and table t2 with their mapping and table t3 with type name and their id's.

I want a result with three columns (area name, pin, ward) i.e the result should be which area comes under pin and ward.

t1:

    --------------------------
    | id  |  area name | type |
    ---------------------------
    | 1   |  a         | 5    |
    | 2   |  b         | 8    |
    | 3   |  x         | 7    |
    | 4   |  z         | 8    |
    | 5   |  pq        | 8    |
    ---------------------------

t2:

    ------------------------------
    | id  |  area_id | parent_id |
    ------------------------------
    | 1   |  2       |   1       |
    | 2   |  2       |   3       |
    | 3   |  4       |   1       |
    | 4   |  5       |   3       |
    -----------------------------

t3:

    ------------------
    | id  |  name    |
    ------------------
    | 5   |  pin     |
    | 7   |  ward    |
    | 8   |  area    |
    ------------------

Result:

    --------------------------
    | area  |  pin   | ward  |
    --------------------------
    | b     |  a     |   x   |
    | z     |  a     |       |
    | pq    |        |   x   |
    --------------------------

Anybody knows how to get this, please help me. I don't know how to get that value. I tried but couldn't find anything.

There are no cases which satisfy JOIN t3 atn ON atn.id = ap.parent_id - so I wouldn't expect it to work. — P.Salmon, Sep 27 '16 at 07:20
Your expected result is not proper according to your conditions. — Jibin Balachandran, Sep 27 '16 at 07:33

score 0 · Accepted Answer · answered Sep 27 '16 at 07:32

0

Just a guess. Pivoting parent name by parent type

SELECT a.name,
    max(case when atn.name = 'pin' then p.name end) as pin
    max(case when atn.name = 'ward' then p.name end) as ward
FROM t2 ap
    JOIN t1 a ON a.id = ap.area_id
    JOIN t1 p ON p.id = ap.parent_id
    JOIN t3 atn ON atn.id = p.type
GROUP BY a.name

answered Sep 27 '16 at 07:32

Serg

22,285
5
21
48

Thanks, but we have to show only area name in area column, so we have to add " and condition of type" ... but thanks for help. :) – sahil0021 Sep 27 '16 at 09:05

Join multiple SQL tables

1 Answers1