CODEWITHSUNDEEP
sqlpostgresql
Alexander Chandra
Alexander Chandra

Reputation: 659

SQL select where not exists

I have 4 table

branch          target         target_dept   dept
--------        ---------      -----------   --------
branch_id       target_id      target_id     dept_id
branch_name     branch_id      dept_id       dept_name

i want to select all branch with dept that has relation here my query

select b.branch_id, b.branch_name,dept.dept_name
from branch b
left join target ptar on ptar.branch_id=b.branch_id
left join target_dept pdept on pdept.target_id=ptar.target_id
left join dept dept on pdept.dept_id=dept.dept_id

I get what i want to get with that query. Lets say the output like this (there are 5 data on dept)

B001 | KUALA LUMPUR | DEPT1
B001 | KUALA LUMPUR | DEPT2
B002 | BALI         | DEPT3
B002 | BALI         | DEPT4
B002 | BALI         | DEPT5
B003 | MANILLA      |

And i want to get the branch with dept that hadnt any relation yet, therefore i used not exists query like this

select b.branch_id, b.branch_name,dept.dept_name
from branch b
left join target ptar on ptar.branch_id=b.branch_id
left join target_dept pdept on pdept.target_id=ptar.target_id
left join dept dept on not exists(select null where pdept.dept_id=dept.dept_id)

the output i want to get is like this

B001 | KUALA LUMPUR | DEPT3
B001 | KUALA LUMPUR | DEPT4
B001 | KUALA LUMPUR | DEPT5
B002 | BALI         | DEPT1
B002 | BALI         | DEPT2
B003 | MANILLA      | DEPT1
B003 | MANILLA      | DEPT2
B003 | MANILLA      | DEPT3
B003 | MANILLA      | DEPT4
B003 | MANILLA      | DEPT5

But the output that i really get is not what i exactly want

Upvotes: 0

Views: 112

Answers (1)

Joe Taras
Joe Taras

Reputation: 15379

If I understand your aim, try this:

This query selects all branches not linked with departments

The strategy:

I apply a cartesian product between branchs and departments, so I've discarded all departements linked to your branch, so remain only the branches without link to departments.

SELECT b.branch_id, b.branch_name,dept.dept_name
FROM branch b
CROSS JOIN dept d
WHERE NOT EXISTS(
    SELECT 'LINKED'
    FROM target T
    JOIN target_dept TD
        ON T.target_id = TD.target_id
    JOIN dept D2
        ON D2.dept_id = TD.dept_id
    WHERE D2.dept_id = d.dept_id
    AND b.branch_id = t.branch_id
)

Upvotes: 1

Related Questions