CODEWITHSUNDEEP
mysqljoin
EMChamp
EMChamp

Reputation: 449

LEFT JOIN resulting in erroneous null records for one column on right table

I'm trying to make a query to join two tables on TABLE_A.CONTENT_PKEY and TABLE_B.REL_PKEY. What I want is to get unique results based on TABLE_A.CONTENT_PKEY and to show TABLE_B.FB_LINE where TABLE_A.CONTENT_PKEY and TABLE_B.REL_PKEY match.

However in my results the column TABLE_B.FB_LINE has all null values. In TABLE_B there isn't a single entry where TABLE_B.FB_LINE is null (confirmed by select statement), so I am confused how it got that result. 

SELECT TABLE_A.PROD_SKU_NUM, TABLE_A.LONG_DESC, TABLE_A.LOCALE_ID, TABLE_B.FB_LINE, TABLE_A.CONTENT_PKEY
FROM TABLE_A
LEFT JOIN TABLE_B ON TABLE_A.CONTENT_PKEY=TABLE_B.REL_PKEY
WHERE (TABLE_A.LOCALE_ID='en_ca' OR TABLE_A.LOCALE_ID='fr_ca')
ORDER BY TABLE_A.PROD_SKU_NUM DESC

Upvotes: 0

Views: 32

Answers (1)

microtony
microtony

Reputation: 192

In a left (outer) join, null values will be added if there the left table's value cannot be found from the right table

For example

A.X  A.Y    B.X  B.Z
3    2      3    5
9    10

A LEFT JOIN B ON A.X = B.X will give

A.X  A.Y  B.Z
3    2    5
9    10   null

I guess you wanted to do a inner join. You can simply remove the word "LEFT".

The expected result will be 

A.X  A.Y  B.Z
3    2    5

Upvotes: 1

Related Questions