Retrieving all data including null values (help!)

[ptr1967]

I am mostly familiar with Oracle syntax and am now dealing with a need to rewrite a portion of sql into MS Access.

I have 4 tables where there may be no data in just 1 of the 4 tables - "skill_assessments" if a user has not provided any data for that question category yet. Every other table/columns has a simple join.

I need to list all rows including the nulls with something like "N/A" if the user's record has no data for it. I definitely got spoiled using the (+) syntax and now feel like a beginner again. If anyone can assist in pointing me in the correct direction Thanks in advance.

SQL - as in Oracle...

SELECT
sa.skillID,
nvl(sa.skill_level_score, 'n/a') AS skill_level_score,
sa.skill_level_score,
s.skilltype,
s.skillname,
tsr.requirement_level

FROM
skills s,
skill_assessments sa,
trades t,
trade_skillrequirement tsr

WHERE sa.userPK = 1
AND s.skilltype = sa.skilltype (+)
AND s.skilltype = tsr.skilltype
AND t.tradePK = tsr.tradePK
AND t.tradepk = 9
ORDER BY s.skilltype ASC

 

[mik]

The 'left join' syntax that Oracle uses seems to work ok in Access

Select Something
From First_Table
Left join Second_Table on First_Table.Join_Attribute = Second_Table.Join_Attribute

Lookup the IIF statement for replacing null with text.

Create a new query in design view and join them together using the user interface to make the join an outer one, then switch to sql mode. You will see sql not to different to whats above.

PS I don't think that 'full join' works so you would need to create a left query and a right query and then union them together.

 

[ptr1967]

adding filters

Thanks.. but Using the query builder i have this as the result. The only issue is that I am unable to filter for a specific userPK. when userPK is added in the where clause, it returns to the result of listing only the records that have values in the skill_assessments table for that user.

SELECT
trade_skillrequirement.requirement_level, trade_skillrequirement.tradepk, skills.skillname, skill_assessments.skill_level_score
FROM
(trade_skillrequirement INNER JOIN skills ON
trade_skillrequirement.skilltype = skills.skilltype)
LEFT JOIN skill_assessments ON
trade_skillrequirement.skilltype = skill_assessments.skillType
WHERE
(((trade_skillrequirement.tradepk)=9));

 

[mik]

Regardless of which dms you us, once you put a value in a where clase it causes an inner join for that value

Solution is to put the condition on the join.

Following example assumes that userpk is in the skills_assessment table and the condition is 11

SELECT
trade_skillrequirement.requirement_level
,trade_skillrequirement.tradepk
,skills.skillname
,skill_assessments.skill_level_score
FROM trade_skillrequirement
INNER JOIN skills
ON trade_skillrequirement.skilltype = skills.skilltype)
LEFT JOIN skill_assessments
ON trade_skillrequirement.skilltype = skill_assessments.skillType
And skill_assessments.userPK = 11
Where trade_skillrequirement.tradepk = 9
;

You will still get all rows from trade_skillrequirement and skills. You will only get values from skill_assessments if the userPK = 11.

Hoipe this helps.