# 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 🙂