Filtering a database with multiple variables

Question

Simply put, I'm trying to filter a database according to multiple selections by the user. The selections are made with a checkboxlist control. The table looks like this:

Table

The aim is to have the user click one or more 'Eis', which will then check the database (The 'Eisen' table, to be exact) to see which 'Systeem' has ALL of them. The problem I have is occuring when more than one 'Eis' is selected. This is due to me not knowing how to properly construct the following query:

SELECT DISTINCT Systeem 
FROM Eisen 
WHERE Eis = {first value from the checkboxlist} AND Eis = {second value from the checkboxlist}

This of course looks for individual rows that fit both the values for Eis, which never fits anything. The code that does this is as follows:

string conString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        string query = "SELECT DISTINCT Systeem FROM Eisen";

        string condition = string.Empty;
        foreach (ListItem item in CheckBoxList1.Items)
        {
            condition += item.Selected ? string.Format("Eis = '{0}' AND ", item.Value) : "";
        }
        if (!string.IsNullOrEmpty(condition))
        {
            condition = string.Format(" where ({0})", condition.Substring(0, condition.Length - 5));
        }
        SqlCommand cmd = new SqlCommand(query + condition);
        using (SqlConnection con = new SqlConnection(conString))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;

                sda.SelectCommand = cmd;
                using (DataSet ds = new DataSet())
                {
                    sda.Fill(ds);
                    GridView1.DataSource = ds;
                    GridView1.DataBind();
                }
            }
        }

My question is as follows. How do I construct the query so that it will look for common occurences in the 'Systeem' column and check for which ones have the required values in the 'Eis' column?

Eric Scherrer · Accepted Answer · 2014-06-13 14:22:03Z

1

Here you go:

SELECT Systeem, count(*)
FROM dbo.Eisen
WHERE Eis IN (1)
GROUP BY Systeem
HAVING count(*) = 1

SELECT Systeem, count(*)
FROM dbo.Eisen
WHERE Eis IN (1, 2)
GROUP BY Systeem
HAVING count(*) = 2

SELECT Systeem, count(*)
FROM dbo.Eisen
WHERE Eis IN (2, 3)
GROUP BY Systeem
HAVING count(*) = 2

Note your third result on your Google table is incorrect - unless I am reading it wrong. Does the Systeem have to contain all of the selected items, or only the selected items?

Edit: as per the comments, here is another scenario:

SELECT Systeem, count(*)
FROM dbo.Eisen
WHERE Eis IN (2, 3)
GROUP BY Systeem
ORDER BY count(*) DESC

edited Jun 13, 2014 at 14:22

answered Jun 13, 2014 at 12:30

Eric Scherrer

3,4091 gold badge21 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user3737607 Over a year ago

This seems to have the same effect as replacing AND with OR. It returns lists of 'Systeem'-occurences that have all or some of the chosen 'Eis'.

Eric Scherrer Over a year ago

I understand now, and it is hurting my brain. Let me take a look and try again, will update answer.

Eric Scherrer Over a year ago

Ok your going to have to group rather than distinct, and then get the count of rows and match that to the count of selections. Working on the query now.

Eric Scherrer Over a year ago

No problem, it helps me clear my mind when I myself get stuck at work on my own problems.

user3737607 Over a year ago

As an extra challenge, how would you go about showing all of the Systeem entries, but ordering them by how many of the Eis-values they match (Descending)? So in the google doc, if I click 2 and 3, it would order the Systeem-values as A-C-B since they match 2-2-1 respectively.

|

Jan Van Herck · Accepted Answer · 2014-06-13 13:00:53Z

0

construct your query with GROUP BY instead of DISTINCT like this:

SELECT
    Systeem 
FROM
    Eisen 
WHERE
    Eis = {first value from the checkboxlist} 
    OR Eis = {second value from the checkboxlist}
GROUP BY
    Systeem
HAVING
    COUNT(*) = { number of checked checkboxes }

answered Jun 13, 2014 at 13:00

Jan Van Herck

2,28417 silver badges16 bronze badges

Collectives™ on Stack Overflow

Filtering a database with multiple variables

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related