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 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!
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'
)