1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2018
    Rep Power

    Filtering Results

    I am currently using the IN statement to filter results from a single column but the results are not exactly what I am looking for. I understand the IN attribute is working as expected I just do not know what I should be using. I have tried everything from a simple UNION to a variety of JOIN statements. The result I would like from the example below is 4 not 3 and 4.

    SELECT * FROM table WHERE column2 IN ('30','36')

    column1 | column2
    3 | 30
    4 | 30
    4 | 36
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Rep Power
    Assuming what you want are results that match BOTH values, you would use IN() in the WHERE clause to determine what data is matched, but you would typically use a query with the following - SELECT your_list_of_columns_here, COUNT(*) as cnt to get a count of rows in each group, GROUP BY column1 to group by the column1 value, HAVING cnt = 2 to filter only the rows in the result set that have a count of 2, i.e. matching both of the IN(...) values.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Toronto Canada
    Rep Power
    SELECT that.column1
         , that.column2
      FROM ( SELECT column1
               FROM table
              WHERE column2 IN (30,36)
                 BY column1 
             HAVING COUNT(DISTINCT column2) = 2 ) AS this
      JOIN table AS that
        ON that.column1 = this.column1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo