Get Ids not exists in another table

Question

I have the following table called TableA

+-----------+--------+--------+
| RequestId | FkLId  | FkSId  |
+-----------+--------+--------+
| 100       | 285301 | 110    |
+-----------+--------+--------+
| 200       | 285301 | 99     |
+-----------+--------+--------+
| 300       | 285301 | 100    |
+-----------+--------+--------+
| 400       | 285301 | 98     |
+-----------+--------+--------+
| 500       | 285301 | 93     |
+-----------+--------+--------+
| 600       | 285302 | 93     |
+-----------+--------+--------+
| 700       | 285302 | 94     |
+-----------+--------+--------+

And also have another table called TableB

+-----------+--------+-------+
| ServiceId | FkLId  | FkSId |
+-----------+--------+-------+
| 500       | 285301 | 109   |
+-----------+--------+-------+
| 501       | 285301 | 99    |
+-----------+--------+-------+

I need to take All the Ids in TableA, not exists in TableB. My Query as follows,

SELECT FkSId
FROM   TableA 
WHERE  FkLId NOT IN (SELECT FkSId FROM TableB) AND FkLId = 285301

But its only returns 110 I need 110,100,98,93 output

Should you change where clause from WHERE FkLID... to WHERE FkSID? — charles, Feb 11 '20 at 05:46
Does this answer your question? [SQL - find records from one table which don't exist in another](https://stackoverflow.com/questions/367863/sql-find-records-from-one-table-which-dont-exist-in-another) — philipxy, Feb 11 '20 at 08:08
This is a faq. Before considering posting please always google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. — philipxy, Feb 11 '20 at 08:10

gotqn · Answer 1 · 2020-02-11T06:13:45.183

2

Try this:

SELECT FkSId
FROM   TableA 
WHERE  FkSId NOT IN (SELECT FkSId FROM TableB WHERE FkLId = 285301) AND FkLId = 28530

edited Feb 11 '20 at 06:13

answered Feb 11 '20 at 05:48

gotqn

42,737
46
157
243

What is the difference of my query and your one? – Sachith Wickramaarachchi Feb 11 '20 at 05:52
1

@Adam You are searching by `FkLId ` - I am filtering by `FkSid` – gotqn Feb 11 '20 at 05:53
still I had the same issue with your query, So I change it as `SELECT FkSId FROM TableA WHERE FkSId NOT IN (SELECT FkSId FROM TableB where FkLId = 285301) AND FkLId = 285301` and problem solved – Sachith Wickramaarachchi Feb 11 '20 at 06:10
@Adam Yes, that makes sense. – gotqn Feb 11 '20 at 06:13

Nguyễn Văn Phong · Answer 2 · 2020-02-11T06:14:10.090

1

Demo on db<>fiddle

The problem is here

WHERE FkSId NOT IN (SELECT FkSId FROM TableB where FkLId = 285301)

You should change from FkLId to FkSId like below

SELECT FkSId 
FROM TableA 
WHERE FkSId NOT IN (SELECT FkSId FROM TableB where FkLId = 285301) AND FkLId = 285301

Output

FkSId
110
100
98
93

edited Feb 11 '20 at 06:14

answered Feb 11 '20 at 05:50

Nguyễn Văn Phong

13,506
17
39
56

@Adam Did you check my answer? https://stackoverflow.com/help/someone-answers – Nguyễn Văn Phong Feb 11 '20 at 05:59
yes, but the problem is still occurred, what I did was I changed the query as follows, `SELECT FkSId FROM TableA WHERE FkSId NOT IN (SELECT FkSId FROM TableB where FkLId = 285301) AND FkLId = 285301` and problem solved – Sachith Wickramaarachchi Feb 11 '20 at 06:09
I got it, you want to filter in TableB. So check my answer again, please – Nguyễn Văn Phong Feb 11 '20 at 06:14

score 0 · Answer 3 · answered Feb 11 '20 at 06:49

0

You need to change FkLId to FkSId. It will solve your problem.

  SELECT FkSId
    FROM   TableA 
    WHERE  FkSId NOT IN (SELECT FkSId FROM TableB) AND FkLId = 285301

answered Feb 11 '20 at 06:49

Prajakta Kale

392
3
19

Get Ids not exists in another table

3 Answers3