SQL Query - Match 1 to 1 in the list provided

Question

I have two tables (Table1 and Table2) such as this:

Table1:

id | X 
1  | 12345
2  | 67890

Table2:

id | Y
1  | 'John Doe'
2  | 'John Doe'
2  | 'Jane Doe'

I want to produce a query such that the end result to be:

id | X     | Y
1  | 12345 | 'John Doe'
2  | 67890 | 'Jane Doe'

But with my query below, the output is as follows as it is matching T1.X IN ('12345', '67890') with all the possible values in T2.Y IN ('John Doe', 'Jane Doe'):

id | X     | Y
1  | 12345 | 'John Doe'
1  | 67890 | 'John Doe'
2  | 67890 | 'Jane Doe'

This was the query:

SELECT *
FROM Table1 T1
INNER JOIN Table2 T2
ON T1.id=T2.id
WHERE T1.X IN ('12345', '67890') 
AND T2.Y IN ('John Doe', 'Jane Doe')

What should the query be such that the result is first ('12345') in the list match with first ('John Doe') only, second ('67890') in the list match with second ('Jane Doe') only without me having to write out individual queries?

Answer 1

If you want specific pairs you can group your conditions in WHERE, use brackets to specify the order and grouping.
Here we want either 1 and 2 are true, which is the first line in your query or 3 and 4 are true which is the third line and we dont want to get 3 and 2 are true.

SELECT *
FROM Table1 T1
INNER JOIN Table2 T2
ON T1.id=T2.id
WHERE (T1.X = '12345' AND T2.Y = ('John Doe')
OR (T1.X ='67890' AND T2.Y = ('Jane Doe') ;

If you only want one value per id and you don't mind which you can use MIN or MAX. In this case we will use MIN because your want 'Jane Doe' in preference to 'John Doe'

SELECT T1.id, MIN(T1.X), MIN(T1.Y)
FROM Table1 T1
INNER JOIN Table2 T2
ON T1.id=T2.id
WHERE T1.X IN ('12345', '67890') 
AND T2.Y IN ('John Doe', 'Jane Doe')
GROUP BY T1.id;