I am trying to get results from two tables using a inner join, this works. However there is a third join, which is the original table, so I used a alias as in table 3 (T3)
But some results are not being returned, when the T3 join is zero, it doesn't return any results.
How do I get around this, I want the record even if the third join does not return any results.
select
'ID' = T1.ID,
'Code' = T1.NOMCode,
'Account Name' = T1.NOMName,
'Group Code' = T1.NOMGroup,
'Group Name' = T2.NOMAccountGroup,
'Combine Code' = T1.NOMCombinedCode,
'Combine Name' = T3.NOMName,
'Comments' = T1.NOMComments
from PARAM_NOMINALCODES T1
inner join PARAM_NOMINALACCOUNTGROUPS T2
on T1.NOMGroup = T2.ID
inner join PARAM_NOMINALCODES T3
on T3.nomcode = T1.nomcombinedcode
where T1.NOMName <> 'Spare' and T1.NOMName <> 'Inactive'
Then you have to use LEFT JOIN instead of INNER JOIN like this:
select
'ID' = T1.ID,
'Code' = T1.NOMCode,
'Account Name' = T1.NOMName,
'Group Code' = T1.NOMGroup,
'Group Name' = T2.NOMAccountGroup,
'Combine Code' = T1.NOMCombinedCode,
'Combine Name' = T3.NOMName,
'Comments' = T1.NOMComments
from PARAM_NOMINALCODES T1
inner join PARAM_NOMINALACCOUNTGROUPS T2
on T1.NOMGroup = T2.ID
LEFT JOIN PARAM_NOMINALCODES T3
on T3.nomcode = T1.nomcombinedcode
where T1.NOMName NOT IN('Spare','Inactive')
BTW instead of two condition use NOT IN to compare to two or more values
INNER JOIN means that only records that answer the ON codition will be returned, everything else will be filtered.
LEFT JOIN means that the (position)left table will return all records and the right table will return only the records that answer the condition
0 comments:
Post a Comment