Advanced Query - also group records with null values

Advanced Query - also group records with null values

  

Hi, I'm not  a specialist in SQL, so I'm in need for some help :-)


I have a pretty simple query:

SyntaxEditor Code Snippet

SELECT {Candidate}.[Recruiter], {Recruiter}.[RecruiterEmail], count(*)
from {Candidate}, {Recruiter}
where {Candidate}.[Recruiter] = {Recruiter}.[Id]
group by {Candidate}.[Recruiter], {Recruiter}.[RecruiterEmail]
order by {Candidate}.[Recruiter]


The resultset only contains records with a value in {Candidate}.[Recruiter].

I also would like the resultset to contain a record with the number of candidates that have NO recruiter, so Recruiter is null (or NullIdentifier in OutSystems).

Is there a way to change the query above to get the desired resultset?

Hi,

You can use a LEFT JOIN for this.

SELECT {Candidate}.[Recruiter], {Recruiter}.[RecruiterEmail], count(*)
from {Candidate} left join {Recruiter} on {Candidate}.[Recruiter] = {Recruiter}.[Id]
group by {Candidate}.[Recruiter], {Recruiter}.[RecruiterEmail]
order by {Candidate}.[Recruiter]

This way, even when a candidate has no Recruiter it will show in the query.

Cheers,

João

Hello Charles, 

I'm not an expert in SQL at all but allow me to offer some advice regarding SQL JOINs:

Avoid doing non-ANSI JOINs (from {Candidate}, {Recruiter} where {Candidate}.[Recruiter] = {Recruiter}.[Id]) and instead use ANSI JOINs (from {Candidate} left join {Recruiter} on {Candidate}.[Recruiter] = {Recruiter}.[Id]). 

Among other reasons it will make sure you don't do a CROSS JOIN by mistake, it is easier to read and it allows you to do different types of JOINs regardless of the database you are working with.
More info about this here and here.

Cheers

João Mateus wrote:

Hi,

You can use a LEFT JOIN for this.

SELECT {Candidate}.[Recruiter], {Recruiter}.[RecruiterEmail], count(*)
from {Candidate} left join {Recruiter} on {Candidate}.[Recruiter] = {Recruiter}.[Id]
group by {Candidate}.[Recruiter], {Recruiter}.[RecruiterEmail]
order by {Candidate}.[Recruiter]

This way, even when a candidate has no Recruiter it will show in the query.

Cheers,

João

Thnx Joao!


João Batista wrote:

Hello Charles, 

I'm not an expert in SQL at all but allow me to offer some advice regarding SQL JOINs:

Avoid doing non-ANSI JOINs (from {Candidate}, {Recruiter} where {Candidate}.[Recruiter] = {Recruiter}.[Id]) and instead use ANSI JOINs (from {Candidate} left join {Recruiter} on {Candidate}.[Recruiter] = {Recruiter}.[Id]). 

Among other reasons it will make sure you don't do a CROSS JOIN by mistake, it is easier to read and it allows you to do different types of JOINs regardless of the database you are working with.
More info about this here and here.

Cheers

Thnx Joao. 


Hello Charles,

Allow me one more advice. AVOID using SQL queries for something that can be achieved with an AGGREGATE, as is the case.

Code will be easier to read and maintain, as well the platform does optimize it for you, but doesn't in the case of a SQL.

Cheers

Eduardo Jauch wrote:

Hello Charles,

Allow me one more advice. AVOID using SQL queries for something that can be achieved with an AGGREGATE, as is the case.

Code will be easier to read and maintain, as well the platform does optimize it for you, but doesn't in the case of a SQL.

Cheers

Of course you may Eduardo ... happy with any good advise I can get :-)

Thnx.