CODEWITHSUNDEEP
sqljoin
Katie Smith
Katie Smith

Reputation: 3

SQL Left Outer Joins displaying Nulls

I've done my best to search for some guidance with this issue but it seems mine is a bit specific.

I have the following SQL Query and it's such a simple join which I believe should work, however when I try to display the 'Description' field from the joining table, it displays every record as NULL. I'm hoping someone might be able to give me some guidance as to where the problems may lie.

So my scenario is, a table of Sales People with their relevant details and then a Region Lookup table...

tblSalesPerson:

ID SPCode CommonName Region
1  bloggsj Joe Bloggs PCNW
2  smiths  Sally Smith PCNE

tblRegionLookup:

ID Region Description
----------
1  PCNW  North West
2  PCNE  North East

So the query I'm trying to write is.....

SELECT 
    sp.ID, sp.Spcode, sp.CommonName, rl.Description
FROM 
    tblSalesPerson AS sp
LEFT OUTER JOIN 
    tblRegionLookup AS rl ON sp.Region = rl.Region
ORDER BY sp.CommonName asc

And the result should be....

ID SPCode CommonName Description
----------
1  bloggsj Joe Bloggs North West
2  smiths  Sally Smith North East

However the Description field is NULL in every field.

I have checked there are no space characters after the text, they are both varchar(10) fields and I've also rebuilt and reorganised the indexes in case they were causing a problem. The ID field on tblSalesPerson is the primary key and Region field on tblRegionLookup is primary key.

Can anyone see what I might be missing?

Thanks in advance.

Upvotes: 0

Views: 57

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Your query is:

SELECT sp.ID, sp.Spcode, sp.CommonName, rl.Description
FROM tblSalesPerson sp LEFT OUTER JOIN 
    tblRegionLookup rl 
    ON sp.Region = rl.Region
ORDER BY sp.CommonName asc;

If rl.Description is NULL, then there is no match between the tables. The problem is that "PCNW" in the first table does not match "PCNW" in the second. A likely cause of this are spaces in the name. You can test this by doing:

    ON replace(sp.Region, ' ', '') = replace(rl.Region, ' ', '')

If this doesn't work, then there are likely other characters in the field, that are either invisible or do not match.

However, that is not a fix to your real problem. The real problem is that you have a foreign key relationship, but you are not using the primary key. If you used the numeric primary key, then this problem would not exist.

So, modify tblSalesPerson to have a RegionLookupId column, which is an integer and refers to tblRegionLookup(id).

Upvotes: 2

Related Questions