query with null values

[MSD]

Hi,

I'm trying to join two tables so that the result contains all the rows in
table A and matching rows in table B. This is what I mean:
Table A contains 2 fields, CatId and CatName:
CatId CatName
1 Apples
2 Bananas
3 Pears

Table B contains the amounts in each category. Some categories may not be in
table B. For example, table B could look like this:
CatId Amount
1 10
3 20

I would like to create a query that results in:
CatName Amount
Apples 10
Bananas Null
Pears 20

My query is: SELECT CatName, Amount FROM TableA LEFT JOIN TableB ON
(TableA.CatId = TableB.CatID);

My result does not contain a row for Bananas. How can I get ALL the
categories to appear in my result, even if they have no value in TableB?

Thanks very much,

Emma

 

[John Vinson]

My query is: SELECT CatName, Amount FROM TableA LEFT JOIN TableB ON
(TableA.CatId = TableB.CatID);

My result does not contain a row for Bananas. How can I get ALL the
categories to appear in my result, even if they have no value in TableB?

Hrm. It should, unless the query you're actually using has a criterion
on some field in TableB; if you have such a criterion it should
include an OR IS NULL clause to cover the case that there might be no
such record. Could you post the actual SQL you're using?

 

[MSD]

Hello John,

Thank you very much. I added an OR IS NULL to the criteria from TableB and
now my query works.

Regards,

Emma