Joins in MS Access

[mohanty2050]

SELECT I.ItemID, I.ItemName, L.PVALUE AS Classification, I.ReorderLevel,
I.Rate
FROM (ItemMaster AS I INNER JOIN CategoryMaster AS C
ON I.CategoryID = C.CategoryID)
LEFT JOIN Poplist AS L ON I.Classification = L.PSHORT
WHERE L.PCODE='ITCLS';


In ItemMaster some entries in Classification column are blank. When I
run this query it lefts the blank records. But I want all the records to be
viewed. What is wrong with this query?

[patjones]

When you do a LEFT JOIN in SQL, such as

SELECT tblA.employeeID AS [ID], tblA.employeeName AS [Name], tblB.employeeBirthDate AS [Date Of Birth]
FROM tblA LEFT JOIN tblB ON tblA.employeeID = tblB.employeeID

...the result set will contain all the records from tblA, and only records from tblB where a corresponding entry can be found according to the join condition. In my example, all the employees will be listed in the first two columns, but if a particular employee either a) has no entry in tblB at all OR b) has an entry in tblB, but date of birth is left blank, then the "Date Of Birth" will show up as blank for them.

In your case, it would appear that there are some records in "ItemMaster" that either do not have a corresponding record in "Poplist", or have a corresponding record, but Poplist.PVALUE is blank.

Pat