• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1706
  • Last Modified:

SIMPLE SQL - INNER JOIN / GROUP - return NULL values in results list

Simple one really - I have some SQL, there is an inner join and a group by the inner join eg:

select count(*) AS Total, myTable.myField FROM myTable INNER JOIN myJoinedTable ON myTable.myField = myJoinedTable.myField GROUP BY myJoinedTable.fieldToGroupBy

At the moment it returns:

a: 3
b: 1
c: 8

etc - but if there are any NULL values in the main table in fieldToGroupBy they get ignored. I cannot think of / find the syntax to just return another row for any null values (or values that do not match the joined table).

can any one fill me in?


Thanks!...
0
niico
Asked:
niico
1 Solution
 
peter57rCommented:
Hello niico,

> etc - but if there are any NULL values in the main table in fieldToGroupBy
fieldToGroupBy would appear to be in the joined table, not the main table



> they get ignored. I cannot think of / find the syntax to just return
> another row for any null values (or values that do not match the joined table).

I don't think I understand what you expect here

For values to be returned from an inner join, there must be a matching record on both sides of the join.
If there is no record in one of the tables then no values will be returned.

The alternative join types would be a left join or a right join.

In your SQL code then instead of 'FROM myTable INNER JOIN myJoinedTable '
you could have
FROM myTable Left JOIN myJoinedTable
This would return values from myTable whether or not there was a matching record in myJoinedTable.

Similarly
FROM myTable Right JOIN myJoinedTable

would return all records from myJoined tabel irrespective of whether there was a matching record in myTable.

Perhaps one of these is what you need?

Pete
0
 
niicoAuthor Commented:
>>fieldToGroupBy would appear to be in the joined table, not the main table

sorry you're right - i meant myField


<yes actually I over simplified - there are two joins.

I'll go away and think about exactly what Im trying to do and come back to you - sorry for the vague question...
0
 
gita_boradCommented:
use a left join?
0
 
LowfatspreadCommented:
1) NULL does not EQUAL NULL

choose an appropriate value for the coalesce and you can group all your "unknown" rows together....

select count(*) AS Total
  , T.Jkey
FROM (select a.*,coalesce(myfield,'XXXXX') as JKey from myTable as A) as T
 INNER JOIN (select a.*,coalesce(myfield,'XXXXX') as JKey myJoinedTable as A)  as J
 ON T.Jkey = J.Key
 GROUP BY J.fieldToGroupBy

0
 
niicoAuthor Commented:
hi

sorry about the delay - this thing ended up getting postponed and havent had a chance to look again.

No objection to giving points to lfs.


thanks!...
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now