Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45

    Unanswered: Select one column in table 1, if it meets all conditions...

    Hello!

    I have two tables:
    Code:
    AdminTable:
    ID  adminID    customerID
    1   4             12
    2   4             13
    3   4             14
    Code:
    CustomerTable
    ID  Car         Location
    12  Ferrari     Earth
    13  Porsche   House
    14  Volvo      Jupiter
    Now I want to grab the "adminID" from the "AdminTable", but only if it has three customers with the cars "Ferrari", "Porsche" and "Volvo", which it has in my example above...

    Ignore the table names and columns, couldn't think of a better example? Sad!

    If you want to see some queries that I've come up with? That's not working? Sure!

    Code:
    SELECT t1.adminID
    FROM adminTable as t1
    WHERE (t1.customerID = (SELECT ID from customerTable WHERE (car = 'Ferrari')))
    OR
    (t1.customerID = (SELECT ID from customerTable WHERE (car = 'Porsche')))
    OR
    (t1.customerID = (SELECT ID from customerTable WHERE (car = 'Volvo')));
    This selects the correct ID's and everything... But I now only want to display those who returned "true" on all those "or"-conditions...

    Suggestions are more than welcome...
    Last edited by ManyTimes; 08-17-10 at 15:52.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT a.adminID
      FROM AdminTable AS a
    INNER
      JOIN CustomerTable AS c
        ON c.ID = a.customerID
       AND c.Car IN ('Ferrari','Porsche','Volvo')
    GROUP
        BY a.adminID
    HAVING COUNT(*) = 3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45
    r937, lovely

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •