Geek Answers Handbook

How to make a select query using an IN condition with an unknown number of values

I have two drop-down lists for the project and a second for the category.
I have to select ddproject, where I get an id based on this id, I have to fill in ddcategory.
Sturcture SQL FIDDLE Table

Here's a simple query if I select ProjectOne from my dropdown

select id,name from tbcategory where id in(1,2)

But my problem is that I do not know how many values ​​will be able to in .

C #: 1st, I split the categoryid column name and get the values ​​later, what to do with the confusion.
Also let me know if you are doing the right thing or in some other alternative way to achieve this.

Edited: Or do I need to change the structure of the table, if so, what should be the schema?

+4
source share
3 answers

According to your information, there are many relationships between a category and a project. Therefore, you need to model a lot more. Many: many tables store data modeling this relationship.

SQL Fiddle Here

First, create a new table to hold the Many: Many link between the category and the project:

 create table tbProjectCategory ( tbProjectId INT, tbCategoryId INT );

Then remove the string-related comma link in tbProject - this is not useful. Instead, paste the links into the Many Many table. eg:.

 insert into tbProjectCategory(tbProjectId, tbCategoryId) values (1, 1), (1, 2);

Link project 1 to categories 1 and 2.

Then, to find all the categories for project 1, you need to join the "Many: many" table, filtering by project ID:

 select cat.id, cat.name from tbcategory cat inner join tbProjectCategory prjcat on prjcat.tbCategoryId = cat.id where prjCat.tbProjectId = 1;

I also deleted my identifier column for brevity - this makes it easier to find related records.

+3
source

I would create an M: N relationship table, for example:

 tbProjects2Categories IDCategory int; IDProject int;

Then you can make a request:

 SELECT c.id, c.name FROM tbCategory c JOIN tbProjects2Categories pc ON c.IDCategory = pc.IDCategory WHERE pc.IDProject = @selectedProjectId;

What will be the relationship between projects and categories:

IDProject | IDCategory

  1 | 1 1 | 2 1 | 3 2 | 1 2 | 3

And that means in the original schema:

 (1,'ProjectOne','Mumbai','1,2,3'); (2,'ProjectTwo','USA','1,3');

To complete the answer:

Storing entity relationships in comma-separated lists is not really good practice. You would even have difficult days to split this column value and make your query work.

+3
source

Yes, comma-separated values ​​are not a good way to store relationships in a database. You must create another table to determine the relationship between the two source tables:

 create table tbProjectCategory (id int IDENTITY(1,1),projectId int, categoryId int)

You can then query it based on projectId as follows:

 select * from tbcategory c inner join tbProjectCategory pc on c.id = pc.categoryId where projectId = 1;

Try this script: http://sqlfiddle.com/#!3/b11acb/2

+1
source

More articles:


All Articles