CODEWITHSUNDEEP
c#asp.netdatabase
user3737607
user3737607

Reputation: 25

Filtering a database with multiple variables

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?

Upvotes: 1

Views: 298

Answers (2)

Eric Scherrer
Eric Scherrer

Reputation: 3388

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

Upvotes: 1

Jan Van Herck
Jan Van Herck

Reputation: 2284

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 }

Upvotes: 0

Related Questions