SQL Assistance - won't select where field is blank.

[Matt Smith]

I'm selecting fields from my main table (ProductData). Each record in that
table has a SupplierIndex.
There is a field called SubCategory, which looks up from
tblSubCatList.iSubCatID but which is not necessarily populated.

My SQL statement is as follows:

SELECT ProductData.DacNumber, ProductData.ProdName & ' ' &
ProductData.SuppliersCatalogueNumber AS ProdName, ProductData.ProdPrice
FROM tblSubCatList INNER JOIN ProductData ON tblSubCatList.iSubCatID =
ProductData.SubCategory
WHERE (((ProductData.SupplierIndex)=1))
ORDER BY ProductData.SubCategory, ProductData.ProdName;

However, it only selects those records whose field SubCategory is populated.
How can I make it pick up those records that have no subcategory?

Any ideas?

Cheers

Matt

PS. The database is Access 2000 but will be converted to MSSQL once I go
live.

[Aaron Bertrand - MVP]

Use a LEFT JOIN instead of an INNER JOIN. Is ProductData.SubCategory blank,
or NULL? There is a very big difference.

A



"Matt Smith" <_matt@breathemail.net> wrote in message
news:bemipd$fpm$1@titan.btinternet.com...

> I'm selecting fields from my main table (ProductData). Each record in that
> table has a SupplierIndex.
> There is a field called SubCategory, which looks up from
> tblSubCatList.iSubCatID but which is not necessarily populated.
>
> My SQL statement is as follows:
>
> SELECT ProductData.DacNumber, ProductData.ProdName & ' ' &
> ProductData.SuppliersCatalogueNumber AS ProdName, ProductData.ProdPrice
> FROM tblSubCatList INNER JOIN ProductData ON tblSubCatList.iSubCatID =
> ProductData.SubCategory
> WHERE (((ProductData.SupplierIndex)=1))
> ORDER BY ProductData.SubCategory, ProductData.ProdName;
>
> However, it only selects those records whose field SubCategory is

populated.

> How can I make it pick up those records that have no subcategory?
>
> Any ideas?
>
> Cheers
>
> Matt
>
> PS. The database is Access 2000 but will be converted to MSSQL once I go
> live.
>
>

[Matt Smith]

"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:u2W$Tx7RDHA.2676@TK2MSFTNGP10.phx.gbl...

> Use a LEFT JOIN instead of an INNER JOIN.
>
> A
>
>

I tried sticking a LEFT JOIN in the query but the result is the same. What
is the correct syntax for this?

(maybe slight variation from last time 'cos I've been messing about)
SELECT ProductData.DacNumber, ProductData.ProdName & ' ' &
ProductData.SuppliersCatalogueNumber AS ProdName, ProductData.ProdPrice,
tblSubCatList.strSubCategory FROM tblSubCatList LEFT JOIN ProductData ON
tblSubCatList.iSubCatID = ProductData.SubCategory WHERE
(((ProductData.SupplierIndex)=1))
ORDER BY tblSubCatList.strSubCategory, ProductData.ProdName;

>Is ProductData.SubCategory blank, or NULL? There is a very big difference.

I don't know. If there's no value it's 'cos I haven't entered any data into
it, and there's no default value or anything.

Matt

[Matt Smith]

Cracked it. Changed LEFT Join to RIGHT join.

Been doing a bit of reading on the two methods. Don't quite see why it works
yet but give me a minute.

Cheers for your help Aaron. You're a star :)

Matt

[Aaron Bertrand - MVP]

LEFT JOIN includes records from the "left" table (on the left side of the
JOIN clause) that may or may not be in the "right" table. RIGHT JOIN is the
opposite.




"Matt Smith" <_matt@breathemail.net> wrote in message
news:beml9s$be3$1@hercules.btinternet.com...

> Cracked it. Changed LEFT Join to RIGHT join.
>
> Been doing a bit of reading on the two methods. Don't quite see why it

works

> yet but give me a minute.
>
> Cheers for your help Aaron. You're a star :)
>
> Matt
>
>