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 ""
SELECT a.Article, a.Description, a.DLV, b.Value from tableA a join tableB b on a.Article = b.Article and b.Attribute = 'material'
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.
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';
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.
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 🙂