How can I query the list of IDs with conditions on couple of fields?

Question

I have the following two tables:

Table A

ID Code1 Code2
--------------
1 2000  1
2 3000  2
3 1000  3
4 2100  1
5 3500  5

Table B

Code1 Code2
-----------
2100  7
2100  1
3000  2
1000  4

I need to get the IDs from Table A that have values of Code1, Code2 equal to values of Code1,Code2 from table B?

I need to get the following result.

ID 
--
2
4

Have you tried anything (like an `inner join`)? – HoneyBadger Feb 24 '20 at 14:41 — HoneyBadger, Feb 24 '20 at 14:41

score 4 · Answer 1 · answered Feb 24 '20 at 14:43

4

Can you not simply do an INNER JOIN on the two values you need to compare?

SELECT t1.ID
FROM tablea t1
INNER JOIN tableb t2 ON t1.Code1 = t2.Code1 AND t1.Code2 = t2.Code2

answered Feb 24 '20 at 14:43

JamesS

2,167
1
11
29

Suraj Kumar · Accepted Answer · 2020-02-24T15:11:04.210

4

You can try this using exists as shown below.

create table TableA (id int, Code1 int, Code2 int)
insert into TableA values
(1, 2000,  1),
(2, 3000,  2),
(3, 1000,  3),
(4, 2100,  1),
(5, 3500,  5)

Create table TableB(Code1 int, Code2 int)
insert into TableB Values
(2100,  7),
(2100,  1),
(3000,  2),
(1000,  4)

Select 
   TableA.Id
from TableA
where exists (Select 1 from TableB where tableA.Code1 = tableB.Code1
and TableA.Code2 = tableB.Code2)

Here is the live db<>fiddle demo. It looks as shown in the below image.

Another way is to use the SQL JOIN and different types of JOINs .

edited Feb 24 '20 at 15:11

answered Feb 24 '20 at 14:57

Suraj Kumar

5,547
8
20
42

1

@SmartDev . . . I prefer `exists` because it is guaranteed not to generate duplicate rows in the first table. – Gordon Linoff Feb 24 '20 at 15:16
1

. . . You should add table alias to make it clear for correlation. – Yogesh Sharma Feb 24 '20 at 15:20

How can I query the list of IDs with conditions on couple of fields?

2 Answers2