How to show two columns in separate rows, avoid duplicates and omit empty entries

Question

I got two tables which I joining the query. The joined table has two columns which I need as separate rows in the query-output; duplicates must be omitted.

So far I got it.

If one of the second column of the joined table is empty, this row must also be omitted. I am failing with this requirement.

create table article (id integer, artnum varchar(100), artname varchar(100), PRIMARY KEY (artnum));
 insert into article (id, artnum, artname) values (1, '100', 'apple');
 insert into article (id, artnum, artname) values (2, '101', 'banana');
 insert into article (id, artnum, artname) values (3, '102', 'clementine');
 insert into article (id, artnum, artname) values (4, '103', 'dragon fruit');
--
create table supplier (id integer, supartnum varchar(100), supplier1 varchar(100), supplier1name varchar(100), supplier2 varchar(100), supplier2name varchar(100), PRIMARY KEY (supartnum));
 insert into supplier (id, supartnum, supplier1, supplier1name, supplier2, supplier2name) values (1, '100', '23', 'DZ', '42', 'VZ');
 insert into supplier (id, supartnum, supplier1, supplier1name, supplier2, supplier2name) values (2, '101', '42', 'ZV', '23', 'DZ');
 insert into supplier (id, supartnum, supplier1, supplier1name, supplier2, supplier2name) values (3, '102', '23', 'DZ', '', '');
 insert into supplier (id, supartnum, supplier1, supplier1name, supplier2, supplier2name) values (4, '103', '23', 'DZ', '23', 'DZ');

select artnum, artname, --supplier1name, supplier2name,
(unnest(ARRAY(SELECT DISTINCT e FROM unnest(ARRAY[supplier1name,supplier2name]) AS a(e)))) as "suppliercombo"
from article
join supplier on artnum = supartnum
;

Result:

| artnum | artname      | suppliercombo |
| ------ | ------------ | ------------- |
| 100    | apple        | VZ            |
| 100    | apple        | DZ            |
| 101    | banana       | ZV            |
| 101    | banana       | DZ            |
| 102    | clementine   |               |
| 102    | clementine   | DZ            |
| 103    | dragon fruit | DZ            |

The first appearance of atrium 102 (clementine) with an empty suppliercombo hat to be omitted. I tried using Union All but failed, because its already a 300line-sql which would be duplicated..

Example is also on DB Fiddle

Answer 1

If one of the second column of the joined table is empty, this row must also be omitted. I am failing with this requirement.

The query can be written as in DB-FIDDLE-1

select artnum, artname, suppliercombo
from (
   select artnum, artname, 
   unnest(array_remove(array[supplier1name, supplier2name], '')) as suppliercombo
  from article
  JOIN supplier ON artnum = supartnum
) t1
group by artnum, artname, suppliercombo
order by artnum;

This gives me the correct output as :

artnum	artname	suppliercombo
100	apple	VZ
100	apple	DZ
101	banana	ZV
101	banana	DZ
102	clementine	DZ
103	dragon fruit	DZ

The above query can be also simplified using union as in DB-FIDDLE-2

select artnum, artname, suppliercombo
from (
  select artnum, artname, supplier1name as suppliercombo
  from article
  join supplier on artnum = supartnum
  where supplier1name != ''
  union
  select artnum, artname, supplier2name as suppliercombo
  from article
  join supplier on artnum = supartnum
  where supplier2name != ''
) t
group by artnum, artname, suppliercombo
order by artnum;

This also gives the expected output as above.