Trying to write an inner join to filter out some conditions

I’m currently struggling with carrying out some joins and hoping someone can shed some light on this. I have three tables: A,B,C

Table C lists names of individuals
Table A lists the food they like to eat
Table B is the link to show what food in A a person likes from C (Our system was built without foreign keys! I know, it’s a pain!)

What I’m trying to write is a query that will return a list of values from Table C which shows the individuals that don’t like a specific food…say PFC

I have the following:

select * from table_c c
inner join table_b b
on c.name = b.bValue
inner join table_a a
on b.aValue = a.number
where a.value not in('PFC')

I’m assuming the joins are working but as table A has multiple values, the two extra rows are being returned. Is it possible to not show this client if one of the joins shows a food I don’t want to see?

Table A
|---------------------|------------------|
|      Number         |     Value        |
|---------------------|------------------|
|          1          |       McDs       |
|---------------------|------------------|
|          1          |       KFC        |
|---------------------|------------------|
|          1          |       PFC        |
|---------------------|------------------|

Table B
|---------------------|------------------|
|      bValue         |     aValue       |
|---------------------|------------------|
|          John       |       1          |
|---------------------|------------------|

Table C
|---------------------|
|      Name           |
|---------------------|
|          John       |
|---------------------|

I’m also using SQL Server 2013 if that makes a difference!

Answer

With NOT EXISTS:

select * from table_c c
where not exists (
  select 1 from table_b b inner join table_a a
  on b.aValue = a.number
  where b.bValue = c.name and a.value = 'PFC'
)

Advertisement

Answer