Get divisionId by passing more tha one classids

[ssreevidya.m]

Hi,
I have 2 tables tbl_class & tbl_Divisions.
structure:

tbl_class tbl_Divisions tbl_class_division
---------- ------------- -------------------
class_Id (pk) division_Id(pk) class_division_id(pk)
class_name division_name class_Id(fk)
division_Id(fk)

In my application I want to select division_Id,division_name by passing more than one class_id.
if I pass 3 class_Id's , I want to get only those divisions that are common to all the 3 classes.
How can i written a query for that.

I used join query , but I got all the divisions the 3 classes have.

Thanks in advance.

[BitBlt]

Your explanation seems to make no sense. Do you have 2 tables or 3? It looks like 3:

tbl_Class: class_id (pk), class_name
tbl_Divisions: division_id (pk), division_name
tbl_class_division: class_division_id (pk), class_id (fk), division_id (fk)

Next, you don't say whether the number of classes you pass is dynamic or not. If it's more than one, can it be two, or three, or four?

Last, you don't say how the class_id's are passed. Is it from somewhere that some dynamic SQL can be constructed? Is it via a table variable? An array? A comma-separated list?

Anyway, here's a statement that will get you what you want, but it's not dynamic. It assumes three class_id's get provided.

select distinct 
Y0.division_id, 
Y0.division_name 
from tbl_divisions Y0
inner join tbl_class_division Y1
on Y0.division_id = Y1.division_id
where exists (select 1 from tbl_class_division Z1 where Z1.division_id = Y0.division_id and Z1.class_id = 1)
and exists (select 1 from tbl_class_division Z1 where Z1.division_id = Y0.division_id and Z1.class_id = 2)
and exists (select 1 from tbl_class_division Z1 where Z1.division_id = Y0.division_id and Z1.class_id = 3)

You can take this statement and figure out how to make it dynamic if you wish. I suggest you do it in code to dynamically construct it. You could also create a stored procedure and dynamically create it as well, but it would be messier.

Good luck! Hope this helps some!

[ssreevidya.m]

Thanks for your replay and sorry for my mistaks in my post.
As you said, the clssess I pass is dynamic. I used a listbox (selection mode - mulltiple)for selecting the classes. Each time the number of classes is different according to listbox selection.
I tried using the query you provided. But I got the result as null. would you please help me to solve it.
Regards.

[BitBlt]

I put together some simplistic sample data and the above query worked fine for me. Without YOUR data, I'd just be guessing. And, without knowing how you assemble your query (as in, what does your code look like) I'd be guessing. Maybe if you post your code, or used a breakpoint and trap the actual query you send to SQL Server, I might be able to assist more.

Otherwise (you guessed it!) I'd be guessing.