Joining on null values only if no other results-CodePudding

Table A	Table B
Column A	Column A	Column B
A	A	1
B	A
C	B	2
	C

Table A and Table B both have Column A. Table B has Column B. The Column A's in Table B have multiple rows for multiple values of Column B. How do I return a result like the below table? Any join that I've tried results in multiple rows for the A value because the table that I'm joining has multiple results. I'd like it to show a result in Column B only if there is a result from Table B and to include null values only if there are no other values.

Results Table
Column A	Column B
A	1
B	2
C

CodePudding user response：

There are numerous ways you can tackle this. You haven't revealed your specific RDBMS but the following correlated sub-query is one way.

select colA, (
  select colB 
  from TableB b 
  where b.colA = a.colA 
  order by v.colB desc 
  limit 1
)
from TableA a;

You may need to use top or offset / fetch instead of limit, depending on your RDBMS.

CodePudding user response：

Schema (MySQL v8.0)

CREATE TABLE table_a (
  `Col_A` VARCHAR(1)
);

INSERT INTO table_a
  (`Col_A`)
VALUES
  ('A'),
  ('B'),
  ('C'),
  ('C');

CREATE TABLE table_b (
  `Col_A` VARCHAR(1),
  `Col_B` INTEGER
);

INSERT INTO table_b
  (`Col_A`, `Col_B`)
VALUES
  ('A', '1'),
  ('A', NULL),
  ('B', '2');

Query

SELECT Col_A, MAX(Col_B) Col_B
FROM table_a
LEFT JOIN table_b USING(Col_A)
GROUP BY Col_A;

Col_A	Col_B
A	1
B	2
C

View on DB Fiddle

CodePudding user response：

You can use:

SELECT column_a,
       column_b
FROM   (
  SELECT a.column_a,
         b.column_b,
         RANK() OVER (
           PARTITION BY a.column_a
           ORDER BY CASE WHEN b.column_b IS NULL THEN 1 ELSE 0 END
         ) AS rnk
  FROM   table_a a
         INNER JOIN table_b b
         ON (a.column_a = b.column_a)
)
WHERE  rnk = 1;

Which, for the sample data:

CREATE TABLE table_a (column_a) AS
SELECT 'A' FROM DUAL UNION ALL
SELECT 'B' FROM DUAL UNION ALL
SELECT 'C' FROM DUAL;

CREATE TABLE table_b (column_a, column_b) AS
SELECT 'A', 1 FROM DUAL UNION ALL
SELECT 'A', NULL FROM DUAL UNION ALL
SELECT 'B', 2 FROM DUAL UNION ALL
SELECT 'C', NULL FROM DUAL;

Outputs:

COLUMN_A COLUMN_B

A 1

B 2

C null

db<>fiddle here