How to join and get 4 results

Question

I have 2 tables

A
artnr grp name
1 FlowerA NameA
2 FlowerB NameB
3 FlowerC NameC
4 FlowerD NameD

B
artnr eigenschap waarde
1 color Red
2 color Null
4 color Yellow
4 Height 30

How do I get 4 results with color?

Select A.Artnr,
       A.grp,
       A.name,
       B.waarde
from A
left join B on B.Artnr = A.Artnr 
where B.Eigenschap = 'color'

My problem is that table B doesn't have artnr 3, Artnr 2 is empty( Is Null) that's no problem. I have problems when a record doesn't exist in both tables.

I would like the following result:

A left join b
artnr grp name   waarde
1 FlowerA NameA  red
2 FlowerB NameB  NULL
3 FlowerC NameC  NULL   -- Droptopp: I want to see this even not in table B
4 FlowerD NameD  yellow

I get 3 rows, missing row with artnr 3 it's because it's not in table B I think. So how do I get a result with 4 rows and the row with artnr 3 an empty cell in the column color?

What do you expect to happen with records "that don't exist in both tables"? What is a record, that does not exists in both tables? — D. Mika, May 17 '17 at 06:36
artnr is same to both table so use alias when selecting data e.g Select A.Artnr as aID, A.grp as grp, A.name as name, B.color as bcolor from A left join B on B.Artnr = A.Artnr — sunil, May 17 '17 at 06:41
I want 4 results I want artnr 3 to show up as well even it is not in both tables. This way I can see what I miss. I don't want table B filled with all records from table A and color as empty (Is Null) — Droptopper, May 17 '17 at 07:42
@Droptopper Left join does just that. Your current SQL will show a line for both `2` and `3` and both will show `color` as `NULL`. — Klas Lindbäck, May 17 '17 at 08:43
What result DO you get? Because you should be getting a row for artnr 3 already. — Klas Lindbäck, May 17 '17 at 09:49
I get 3 rows, missing row with artnr 3 it's because it's not in table B I think. So how do I get a result with 4 rows and the row with artnr 3 an empty cel in the kolom color.. — Droptopper, May 17 '17 at 12:57
I changed table B, it's now how it is.. I want the 4 rows from table A and the colors out of table B if exist or else Null — Droptopper, May 17 '17 at 14:03

score 0 · Answer 1 · edited May 23 '17 at 12:26

0

SELECT A.Artnr, A.grp, A.name, B.waarde
FROM A 
LEFT OUTER JOIN B 
ON (A.artnr = B.artnr)

What is the difference between "INNER JOIN" and "OUTER JOIN"?

edited May 23 '17 at 12:26

Community

1
1

answered May 17 '17 at 14:18

Galeaettu

74
7

score 0 · Accepted Answer · answered May 17 '17 at 14:21

Your problem seems to be the WHERE. This statement:

Select A.Artnr,
   A.grp,
   A.name,
   B.eigenschap,
   B.waarde
from A
left join B on B.Artnr = A.Artnr

would return a row for article 3, which is

3 FlowerC NameC NULL NULL

after that you filter for eigenschap = 'color', which removes that row from the final result set since eigenschap is NULL. Move that condition to the join condition:

Select A.Artnr,
   A.grp,
   A.name,
   B.waarde
from A
left join B on B.Artnr = A.Artnr AND
B.eigenschap = 'color'

How to join and get 4 results

2 Answers2