CODEWITHSUNDEEP
sqloracle-database
Ismail Sensei
Ismail Sensei

Reputation: 207

Ignore SQL INNER JOIN if specific record exist?

i got two table joined like this

SELECT A.id,B.status
FROM TableA A
INNER JOIN TableB B on (A.TableA_ID=B.TableA_ID)
WHERE B.status not in ('CO','CL');

I want to display results of two table joined but with condition if the status of TableB has anything in ('CO','CL') the whole join with id of TableA would be ignored not just the rows with status not in ('CO','CL').

A.id and A.TableA_ID are different columns

Original result without where condition would be like this:

+------+-----------+
| id   | status    |
+------+-----------+
| 1000 | RE        |
| 1000 | RE        |
| 1000 | RE        |
| 1000 | CO        |
| 2000 | RE        |
| 2000 | RE        |
+------+-----------+

My Result:

+------+-----------+
| id   | status    |
+------+-----------+
| 1000 | RE        |
| 1000 | RE        |
| 1000 | RE        |
| 2000 | RE        |
| 2000 | RE        |
+------+-----------+

What i want:

+------+-----------+
| id   | status    |
+------+-----------+
| 2000 | RE        |
| 2000 | RE        |
+------+-----------+

Couldn't figure out how to do eliminate the whole join if the record 'CO' exist.

Upvotes: 0

Views: 549

Answers (2)

eshirvana
eshirvana

Reputation: 24593

here is one way :

SELECT A.id,B.status
FROM TableA A
INNER JOIN TableB B on (A.TableA_ID=B.TableA_ID)
WHERE not exists ( select 1 from TableB B where A.TableA_ID=B.TableA_ID and B.status in ('CO','CL')

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191425

You could use not exists:

SELECT A.id,B.status
FROM TableA A
INNER JOIN TableB B on (A.TableA_ID=B.TableA_ID)
WHERE NOT EXISTS (
  SELECT null
  FROM TableB B
  WHERE B.TableA_ID=A.TableA_ID
  AND B.status in ('CO','CL')
);

Or if you only want to hit the tables once you could use an analytic count of the of the statuses you don't want to see, and eliminate any IDs with a non-zero count:

SELECT id, status
FROM (
  SELECT A.id,B.status,
    COUNT(case when B.status in ('CO','CL') then 1 end)
      OVER (partition by A.id) AS cnt
  FROM TableA A
  INNER JOIN TableB B on (A.TableA_ID=B.TableA_ID)
)
WHERE cnt = 0;

db<>fiddle

This assumes A.id and A.TableA_ID are different columns; if they're the same then you don't need to look at table A directly at all, if you only want those two columns anyway - all of the information you need is in table B anyway.

Upvotes: 1

Related Questions