1

I am trying to work out a query where by it returns the employees who have not being assigned a job yet.

I've tried inner join query but it hasn't worked for me. Help is much appreciated on this :)

The two tables are as follows:

employee

•   id [varchar]
•   employee_name [varchar]
•   department [varchar]

job

•   id [varchar]
•   factory [varchar]
•   employee_name [varchar]

There is more dimensions in these two tables but iv only included the relevant ones.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Ryan Dennehy
  • 33
  • 5

1 Answers1

0

Left joins return all data including nulls (where this is no data to be had)

Use that over an inner join.

Select e.* from employee e -- Select only the data from the employee table (that's all we care about)
left join job j on e.employee_name = j.employee_name -- This will return all employee data no matter if they have a job or not
where j.id is null -- We are looking for employees that have no job currently

SQL Join explanation

berkobienb
  • 639
  • 5
  • 12
  • 1
    Great this has worked great !! Dont know why i didnt use left join course it makes sense now. – Ryan Dennehy Oct 27 '20 at 17:24
  • I also have the employees "address" in the table. Is there an operand in SQL that selects all the employees with the same address. Rather than going about it a conventional way ? – Ryan Dennehy Oct 27 '20 at 17:25
  • You'd have to use the COUNT() function https://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table?rq=1 – berkobienb Oct 27 '20 at 17:31
  • SELECT employee_name, address COUNT(*) as C FROM employee group by address having COUNT(*) >1 order by C – Ryan Dennehy Oct 27 '20 at 17:35
  • something like this ?? or am i off the ball – Ryan Dennehy Oct 27 '20 at 17:35
  • SELECT e.employee_name, e.address FROM employee e INNER JOIN (SELECT address, COUNT(*) FROM employee GROUP BY address HAVING count(*) > 1 ) b ON e.address = b.address ORDER BY e.employee_name – berkobienb Oct 27 '20 at 17:40
  • Sorry for this but im also wondering on one more thing !! I have male (boolean), female (boolean) and country as dimensions also and im trying to retrieve which country has the second highest percentage of female workers. I tried getting the top which i think worked out but the second highest is troubling me. – Ryan Dennehy Oct 27 '20 at 18:10
  • yes it did and thanks, I marked it as correct im sure ? I actually replied to that also and didnt send so apologies – Ryan Dennehy Oct 27 '20 at 18:13