Left Outer Join possible?

All we need is an easy explanation of the problem, so here it is.

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!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Method 2

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';

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: ,

Leave a Reply