niico
asked on
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.fieldToGroup By
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!...
select count(*) AS Total, myTable.myField FROM myTable INNER JOIN myJoinedTable ON myTable.myField = myJoinedTable.myField GROUP BY myJoinedTable.fieldToGroup
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!...
ASKER
>>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...
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...
use a left join?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!...
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!...
> 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