Yogi - 6 months ago 25

SQL Question

Let me try to explain the scenario. I have two tables A (Columns - A1, A2, A3) & B (Columns - B1, B2, B3). I need to join table A with table B on A1.B2. For every join, table B has one or two records with different values for B3(X or Y). I wanna write one query where the JOIN query needs to pick the row with B3=X(if there's no other row with B3=Y); If two rows exists (B3=X & B3=Y), then the query needs to pick only the row with B3=Y (ignoring the row with B3=X).

Let me try to give some values to the tables & explain a little bit more.

Table A

********

A1 A2 A3

1 11 111

2 22 222

3 33 333

4 44 444

Table B

********

B1 B2 B3

6 1 X

7 1 Y

8 2 X

9 3 X

10 3 Y

11 4 X

Again.. JOIN is on A1.B2. The result should be as following,

JOIN Results

*************

A1 A2 A3 B1 B2 B3

1 11 111 7 1 Y

2 22 222 8 2 X

3 33 333 10 3 Y

4 44 444 11 4 X

Let me know if you guys have any clarification about my question.

Thanks in advance.

Yogi

Answer

You can pick the rows from table B with the `ROW_NUMBER`

function if you partition by the join column and order by your "picking order" column:

```
SELECT b1, b2, b3,
ROW_NUMBER() OVER (PARTITION BY b2 ORDER BY b3 DESC) as rn
FROM b;
1 Y 1
1 X 2
2 X 1
3 Y 1
3 X 2
4 X 1
```

Then you can filter the first row, the one with `rn=1`

:

```
SELECT b1, b2, b3
FROM (SELECT b1, b2, b3,
ROW_NUMBER() OVER (PARTITION BY b2 ORDER BY b3 DESC) as rn
FROM b)
WHERE rn=1;
7 1 Y
8 2 X
10 3 Y
11 4 X
```

The filtered rows can then be joined to table a:

```
SELECT *
FROM a
JOIN (
SELECT b1, b2, b3
FROM (SELECT b1, b2, b3,
ROW_NUMBER() OVER (PARTITION BY b2 ORDER BY b3 DESC) as rn
FROM b
)
WHERE rn=1
) bfilter ON a.a1 = bfilter.b2;
1 11 111 7 1 Y
2 22 222 8 2 X
3 33 333 10 3 Y
4 44 444 11 4 X
```

If 'X' and 'Y' are not actual values, you can extend the `ORDER`

clause with a `CASE`

statement to allow for general values:

```
ROW_NUMBER() OVER (PARTITION BY b2 ORDER BY
CASE b3 WHEN 'Y' THEN 1
WHEN 'X' THEN 2
...
END ASC)
```

Edit:

```
SELECT a1, a2, a3, b1, b2, b3
FROM (
SELECT a1, a2, a3, b1, b2, b3,
ROWNUMBER() OVER (PARTITION BY a1 ORDER BY
CASE WHEN a2=... AND b3=... THEN 1
WHEN a2=... AND b3=... THEN 2
...
END ASC)
FROM a JOIN b ON a.a1 = b.b2
)
WHERE rn = 1;
```