0

i have a problem which needs to get resolved and i hope some of you can help.

i have an article table (Table A)

Article     Description
A43234      desk 
A42323      paper
A43235      chair

and a attribute table (Table B)

Attribute      Article      Value
height         A42323       120cm
width          A42324       200cm
material       A42323       wood
price          A43235       chair

not every article has an attribute in table B. But if there is a material attribute entry for an article of Table A then it should be in the result. If there is no material attribute entry then it should be an empty string. The result should be:

Article      Attribute        Value
A42323       paper            wood
A42324       desk             ""
A42325       chair            ""

My idea:

SELECT a.Article, a.Description, a.DLV, b.Value  
from tableA a 
join tableB b
on a.Article     = b.Article 
and b.Attribute = 'material'

Thanks!

3
  • What if there are two rows with 'material' and the same artcicle? Jul 15 at 22:35
  • 1
    But you are right, you only need to change the join to left: from a left join b on .. Jul 15 at 22:37
  • two rows with 'material' is not possible. they are unique. but i can be, tha there is no row for this article. so will a left join work?
    – Andre
    Jul 16 at 9:18
1

I understand that you want material record for every article even if such record is not present in attribute table.

You can split your select to 2 parts:

  • material in one - to get material for all articles
  • all other attributes in second

Then you can do UNION:

SELECT
    a.Article,
    a.Description,
    'material' AS Attribute,
    COALESCE(b.Value, '') AS Value
FROM article AS a
LEFT JOIN attribute AS b ON a.Article = b.Article
                             AND b.Attribute = 'material'
UNION ALL
SELECT
    a.Article,
    a.Description,
    b.Attribute,
    b.Value
FROM article AS a
LEFT JOIN attribute AS b ON a.Article = b.Article
                             AND b.Attribute <> 'material';

Output:

Article     Description Attribute   Value
A43234      desk        material    ""
A42323      paper       material    wood
A43235      chair       material    ""
A43234      desk        NULL        NULL
A42323      paper       height      120cm
A43235      chair       price       chair

I used T-SQL and also changed JOIN to LEFT JOIN as pointed in comments, because you loose articles without attributes.

3
  • here it is for aliases only, if a string is a keyword in t-sql it is good practice to use these brackets, you can ignore them
    – owl
    Jul 16 at 8:54
  • T-SQL uses them, I don't know Progress
    – owl
    Jul 16 at 9:16
  • Worked! Thanks!
    – Andre
    Jul 16 at 11:28
1

You are on the right way, simply change your JOIN by a LEFT JOIN

select a.Article, a.Description, a.DLV, b.Value  
from tableA a 
left join tableB b
    on a.Article     = b.Article 
    and b.Attribute = 'material';

Not the answer you're looking for? Browse other questions tagged or ask your own question.