0

I need help with a join/script that needs to reference both keys in order to narrow the data.

Below is a sample of the data that I'm working with:

OnboardingID    AboutTypeID ReferenceID OnboardingState
1                   5           1009         6
2                   5           1010         5
3                   6           132456       6
4                   6           178945       6
5                   5           1011         4
6                   6           165489       5

The AboutTypeID equates to the EmployeeID (6) and their requisition/job ID (5). In other words, ReferenceID #132456 (employee) is also related to ReferenceID #1009 (their requisition).

How can I write the join to determine, based upon the OnboardingState column and other joins needed, to narrow down when a particular employee has a requisition onboardingstatus of 5, but also has an employee onboardingstatus of 6? You can see in the fake joins below how they all tie together.

inner join dbo.employee e on dbo.onboardings.referenceID = e.employee_Id
inner join dbo.requisition r on dbo.onboardings.referenceid = r.requisitionID
dbo.requisition.EmployeeID = dbo.employee.employeeID
burghman217
  • 13
  • 2
  • 1
    a join is just a boolean condition, so you can use an "AND" in the join condition...x.a = x.b AND y.a = y.b – Jeremy May 18 '20 at 19:31
  • Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Stop trying to code your overall goal & explain what you expected instead from the given code & why. – philipxy May 18 '20 at 19:42
  • Adding to the 1st comment: `x join y on c` is `x cross join y where c`, the pairs formed from the inputs that meet condition `c`. So--which pairs do you want? PS When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. PS Ask 1 specific researched non-duplicate question per post. PS "You can see in the fake joins below how they all tie together" No, we can't, wrong code is wrong & we can't read your mind about what you wish it said. It's good that you used natural language though. – philipxy May 18 '20 at 19:46
  • Possible duplicate of [Select values that meet different conditions on different rows?](https://stackoverflow.com/q/477006/3404097) Before considering posting please read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. PS Clearly your question can be expected to be a beginner faq. – philipxy May 18 '20 at 19:57

0 Answers0