CODEWITHSUNDEEP
sqlsql-serverforeign-keys
Bok
Bok

Reputation: 587

Select SQL table content based on content in foreign table

I have a database holding informations about different jobs. The jobs can either be for internal customers or external customers.
I need to select the rows in the table Job, which points to a record in customer where isInternal is set to true

I've tried to use innerjoins:

select Job.* from Job as Job
INNER JOIN Task as Task
ON Job.JobID = Task.JobID 
Inner Join Customer as Customer
ON Task.CustomerID = Customer.CustomerID

But this way i will end up with a lot of duplicates in the job.
I tried to use distinct as well, but i end up with less rows than i actually have.

Can anyone point me in the right direction regarding how to approach this kind of task with sql?

In the end this will be used in a SSIS package, for loading data into a staging layer of a DWH

Upvotes: 1

Views: 32

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

If you want jobs where any task has an internal customer, you can use exists:

select j.*
from Job
where exists (select 1
              from Task t join
                   Customer c
                   on t.CustomerID = c.CustomerID
              where j.JobID = t.JobID and
                    c.isInternal = 1
             );

Upvotes: 1

Related Questions