CODEWITHSUNDEEP
sql
Minty
Minty

Reputation: 25

SQL Inner Join - Not returning all records

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'

Upvotes: 1

Views: 3444

Answers (2)

J Petersen
J Petersen

Reputation: 162

Changing your inner join on T3 to a left join should fix your problem.

Edit: Actually, can you try using T3 as the table you are selecting from and left join the other two tables to it? Like so:

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 T3
left join  PARAM_NOMINALCODES T1
on T3.nomcode = T1.nomcombinedcode
left join PARAM_NOMINALACCOUNTGROUPS T2
on T3.nomvode = T2.ID
where T1.NOMName <> 'Spare' and T1.NOMName <> 'Inactive'

Upvotes: 2

sagi
sagi

Reputation: 40481

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

Upvotes: 3

Related Questions