Combine rows with null columns based on other common columns on SQL

Question

I'm running an SQL query on a dashboarding tool that gives a table result looking as follows :

article_name   color    spec1   spec2
article1       brown     0 
article2       blue      1       
article2       blue              3

My query :

select a.name as article_name
       c.clr as color
       d.sp  as spec1
       k.sps as spec2
from table_a a 
left join table_c c on a.id=c.id
left join table_d d on a.id=d.id
left join table_k k on a.id=k.id;

I want to edit that query to make me have a result like this :

article_name   color    spec1   spec2
article1       brown     0 
article2       blue      1       3

Any idea on how I can reach that result ?

Answer 1

Obviously untested, however try grouping and aggregating the spec1/2 columns:

select a.name as article_name
       c.clr as color
       max(d.sp)  as spec1
       max(k.sps) as spec2
from table_a a 
left join table_c c on a.id=c.id
left join table_d d on a.id=d.id
left join table_k k on a.id=k.id
group by a.name, c.clr;