1

I have problem writing a simple query/join. I assume the query should be possible to write as single query, but I have not succeeded. I have tables and data like below:

EMPLOYEE

empid name
joebar Joe
tommyt Tommy
alexia Alexia

JOB

jobid name
1 1st
2 2nd
3 3rd

JOBRESOURCES

empid jobid date
joebar 1 21/1/1
tommyt 1 21/1/1
tommyt 2 21/1/3
alexia 3 21/1/4

How can I write the logical query "List all resources and check if they are assigned to job 1?"

So the result should be:

empid jobid
joebar 1
tommyt 1
alexia (null)

I have tried:

SELECT e.empid, jr.jobid 
FROM employee e 
    LEFT JOIN jobresources jr ON e.empid=jr.empid 
WHERE jr.jobid = 1 
OR jr.empid IS NULL

but the row "alexia" is missing. I assumed I can get all rows from employee and "rich" the rows with left join, but it seems not to work.

SOS
  • 6,430
  • 2
  • 11
  • 29
zeeber
  • 21
  • 2
  • 6
    Like: `SELECT employee.empid, jobresources.jobid FROM employee LEFT OUTER JOIN jobresources ON employee.empid = jobresources.empid AND jobresources.jobid = 1;`? (Just read your attempt).. I think I have this correct. Just move your WHERE conditions into your `ON` conditions instead. That feels counterintuitive at first, but putting conditions on your left-table in the `WHERE` clause causes your `LEFT OUTER JOIN` to become an implicit `INNER JOIN`. – JNevill Mar 07 '22 at 17:48
  • What's the purpose of table `Job`? Is it relevant to your query - your desired results don't seem to need it? – Stu Mar 07 '22 at 18:04
  • JNevill, thats it! Totally forgot that rule, putting the filter to "ON" part for the left join. – zeeber Mar 07 '22 at 18:19

0 Answers0