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

    Join Date
    Feb 2018
    Posts
    12
    Rep Power
    0

    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
    Posts
    143
    Rep Power
    66
    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
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    Code:
    SELECT that.column1
         , that.column2
      FROM ( SELECT column1
               FROM table
              WHERE column2 IN (30,36)
             GROUP
                 BY column1 
             HAVING COUNT(DISTINCT column2) = 2 ) AS this
    INNER
      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