Retrieving all data including null values (help!) (1 Viewer)

P

ptr1967

Guest
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:54
Joined
Feb 19, 2002
Messages
32,833
Jet uses the newer, specific join syntax. Your posted SQL will produce a cartesian product that is filtered by the where clause. This type of join is NOT updatable and is also very slow.

Since you are unfamiliar with Jet syntax, the easiest thing to do is to build your queries with QBE. Just add your tables and/or queries. Draw join lines between them (the default will be inner joins which you can change to left or right as necessary), and select the columns from each table. Add your criteria in the criteria line for each field. All conditions on the same row will be ANDed. Conditions from each row will be ORed. So if you want condition a to be true and condition b to be true, put both experssions on the same criteria row. If you want a or be to be true, put each expression on a different line. You can switch to SQL view at any time and work in that view if you are more comfortable. The big negative with the QBE is that it won't retain any of your text formatting in the SQL so don't invest a lot of time on making it look neat.
 

mik

Lost in a VBA Jungle
Local time
Tomorrow, 05:54
Joined
Nov 16, 2004
Messages
22
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.
 
P

ptr1967

Guest
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

Lost in a VBA Jungle
Local time
Tomorrow, 05:54
Joined
Nov 16, 2004
Messages
22
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.
 

Users who are viewing this thread

Top Bottom