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|tabl e_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]

Solution to Howto get multiple wheres on one column

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]

It worked!

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