Click here to Skip to main content
15,557,873 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Rid	dept	ID
1	1	1
2	6	2
3	2	3
4	3	4
5	1	1
6	2	2
7	3	3
8	4	4
9	1	1
10	2	2
11	3	3
12	7	4

Hi All, I have a scenario where the following two lines in a table, if the value in the Dept column is anything except the distinct values from column Id, it should be showing the values in result.

For example, if the id selected is 1 it should check if the corresponding values in Dept column is anything except(1,2,3,4), if yes list that and same goes for all the values in column Id.

In case of Id=2, the visible row should be Dept 6 ID 2 as Dept code is not from distinct values from ID.

What I have tried:

select DO1.dept,DO2.ID ,* from Table1 DO1 left join Table2 DO2
on DO1.Rid=DO2.Rid
where DO1.dept<>DO2.ID 
Updated 28-Mar-22 7:26am

Share this answer
PreetMDX 28-Mar-22 12:16pm    
I have tried that already but that doesn't provide the data which I'm expecting. The resultset would be like this:

dept ID
6 2
7 4

for each ID I'm trying to compare the departments.
Not sure if I understand correctly but perhaps something like
select mt.dept,
from mytable mt
where mt.dept not in (select distinct 
                      from mytable mt2)
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900