-1

Currently I am working on a project, and I got stuck to show my results. Please can someone help me out.

I have two tables, Jobs and Employees

Jobs                                     Employees
| JobID    | JobDescr | EmployeeID |     | EmpID    | EmpName  | EmpSub     |
| -------- | -------- | ---------- |     | -------- | -------- | ---------- |
| 1        | Job text | 0005       |     | 0001     | Name 1   | 0003       |
| 2        | Job text | 0008       |     | 0002     | Name 2   | 0004       |
| 3        | Job text | 0003       |     | 0003     | Name 3   | 0001       |
| 4        | Job text | 0004       |     | 0004     | Name 4   | 0004       |
| 5        | Job text | 0001       |     | 0005     | Name 5   | 0001       |

I want to show the job results of the current user (EmID 0001) and his substitute.

Results of current user (EmpID 0001)
| JobID    | JobDescr | EmpName |
| -------- | -------- | ------- |
| 1        | Job text | Name 5  |
| 3        | Job text | Name 3  |
| 5        | Job text | Name 1  |

This is what I tried so far

SELECT JobID, JobDescr e.EmpName FROM Jobs 
LEFT OUTER JOIN Employees AS e ON Jobs = EmployeeID
WHERE EmployeeID = {USERID} AND ... 
ORDER BY JobID, EmployeeID

How to continue? Please help and thanks!

Damien
  • 1
  • 2
  • 1
    Your sample data is inconsistent. 0008 is not found in Employees. (Or is it the empsub value EmployeeID references?) – jarlh Dec 29 '22 at 11:51
  • 1
    What is USERID? where is it coming from? It feels like this is missing information. This is a trivial join, but where are you defining what a USERID is? Also, you're not going to output any field from the e table. – Dasph Dec 29 '22 at 11:57
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community Dec 29 '22 at 14:49

1 Answers1

0

If you want jobs by a particular user and by their subs, you can use UNION ALL to combine two queries. This isn't the only way to do it, but it's clean.

select * from jobs where employeeid = {USERID}
union all
select * from jobs where employeeid IN (SELECT empsub FROM employees where empid = {USERID})
Paul W
  • 5,507
  • 2
  • 2
  • 13
  • This answer was reviewed in the [Low Quality Queue](https://stackoverflow.com/help/review-low-quality). Here are some guidelines for [How do I write a good answer?](https://stackoverflow.com/help/how-to-answer). Code only answers are **not considered good answers**, and are likely to be downvoted and/or deleted because they are less useful to a community of learners. Please [edit] your answer to include an explanation of how and why the code solves the problem, when it should be used, what its limitations are, and if possible a link to relevant documentation. – ljmc Dec 29 '22 at 15:47