Inner Join in my SQL

Question

My attempt is to return the information of those people who have emails with "is_read=0":

 SELECT 
        a.*, b.*,c.*, COUNT(a.EmailNum) as NumEmails
    FROM 
        Emails.emails a
    inner join 
        Employees.user b on a.ID=b.id 
    inner join 
        Employees.dept c on b.dept=c.deptid
    where 
        a.is_read='0'and c.deptName='HR';
    group by 
        b.id

Emails. emails(Table):

   emailNum, id, emailcontent,receivers
   1       , 11, "sasa"      ,sa@gmail.com

Employees:

   user(Table)
   userId, firstName, lastName, dept
   1`    , 'John'   ,  'Brown', 55

Employees:

       dept(Table)
   deptId, deptName
   55    , 'HR'  

My attempt gives:

  emailNum, id  ,  emailcontent, receivers,   userId, firstName, lastName, dept, deptId, deptName
   null   , null,  null        ,  null    ,    null ,    null  ,  null   , null  ,  55  , 'HR' 

But I expect it does not return anything!

Thanks

Answer 1

You have a ; between your WHERE clause and your GROUP BY:

where 
    a.is_read='0'and c.deptName='HR';
group by 

This is splitting your query into two... the first part runs without the GROUP BY, the second part gives you an error.

When the query runs without the GROUP BY you get a row with NULL values instead of an empty result set because of the aggregate function.

Remove the ;, and you should get the expected results.

SQL Fiddle with the ;.

SQL Fiddle without the ;.

Answer 2

Try this, Inner joins generally are nested within one another

SELECT 
    a.*
   ,b.*
   ,c.*
   ,COUNT(a.EmailNum) as NumEmails

FROM 
   Emails.emails a
       inner join Employees.user b 

            inner join Employees.dept c 
            on b.dept=c.deptid

       on a.ID=b.id 

WHERE 
   a.is_read='0'
   and c.deptName='HR';
group by 
   b.id

( Also, try grouping by a., b., c.* )