query with null values

  • Thread starter Thread starter MSD
  • Start date Start date
M

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
 
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?
Click to expand...

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?
 
Hello John,

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

Regards,

Emma
 
Back
Top