Howto get multiple wheres on one column

[Idrisu]

Hello,

i have this constellation on tables in my database:

Table A
id|content

Table B
table_a_id|table_c_id

Table C
id|name

My question is:
How to write a select like this:
Select content from table_a inner join table_b on (table_a.id = table_b.table_a_id)
inner join table c on (table_b.table_c_id = table_c.id)
Where name = "nameA" and name = "nameB".

So that i can get that row's from Table A that matches the two names in this n:m constellation. With this Sql-Statement i get of course nothing, with an or i get the row's from Table A with either nameA or nameB.

Thank's for every answer.

[nbiswas]

Try this

Sample data for TableA

id1    content
1    content1
2    content2
3    content3
4    content4
5    content5

Sample data for TableC

id2    name
101    name1
202    name2
303    name3
404    name4
505    name5

Sample data for TableB

id1    id2
1    101
1    202
1    303
1    505
2    101
2    404
3    303
4    101
4    202
4    404
5    101

Now I want to choose those records from TableA which has got both 'Name1' and 'Name2' in TableC.

I such a case, the answer should be 1 & 4

Query 1

select a.id1, a.content from tableA a
join (
select b.id1
from tableC c
join tableB b on c.id2 = b.id2 
where c.name = 'name1' or c.name = 'name2' 
group by b.id1 
having COUNT(c.name) > 1) x
on a.id1 = x.id1 

Query 2:

select a.id1, a.content from @tableA a where a.id1 in(
select b.id1
from @tableC c
join @tableB b on c.id2 = b.id2 
where c.name = 'name1' or c.name = 'name2' 
group by b.id1 
having COUNT(c.name) > 1) 

Output:

id1    content
1    content1
4    content4

Hope this helps.
Let me know in case of any concern.

[Idrisu]

Thank you for the great answer.
After seeing your sql-statements i realize that i'm a total noob on sql ;)

I found also one solution:

Select tableA.id1 from (
Select tableA.id1 as id1A from tableA inner join tableB on (tableA.id1 = tableB.id1) inner join tableC on (tableB.id2 = tableC.id2) where tableC.name = "Name1") as a
inner join (
Select tableA.id1 as id1B from tableA inner join tableB on (tableA.id1 = tableB.id1) inner join tableC on (tableB.id2 = tableC.id2) where tableC.name = "Name2") as b on (id1A = id1B)
... for every tableC.name more i would need one join more

But i will use your's because it's mutch easier and faster.

In your solution it's also helpful to now, that you need to raise the count at COUNT(c.name) for every c.name you need more to match, example:

1. select a.id1, a.content from tableA a
2. join (
3. select b.id1
4. from tableC c
5. join tableB b on c.id2 = b.id2
6. where c.name = 'name1' or c.name = 'name2' or c.name = 'name3'
7. group by b.id1
8. having COUNT(c.name) > 2) x
9. on a.id1 = x.id1