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

    Join Date
    Aug 2012
    Posts
    6
    Rep Power
    0

    General SQL question


    I have a pretty basic problem, but not too sql savy. I will keep it simple with the example vs using my real tables.

    Imagine a table 'student' with columns 'id', 'name'.
    Also a table 'classes' with columns 'id', 'student_fk', 'class_type'

    student_fk is hopefully clearly the foreign key to the student.id primary key. I know this is not great design, but for the sake of this example it will do.

    What i am looking for is a query to select only those students that have one class. Also that one class needs to be of a certain class_type.

    The idea is that the student may have many classes of different types and i only want to find student that have ONLY 1 class associated with them of that type. If the student has a class of that type, but also has another class of a different type i don't want that student returned.

    Hopefully this description makes sense. Any help is appreciated. Thanks in advance.
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,918
    Rep Power
    1045
    Hi,

    Join both tables, group by the student ID and then select those students with exactly one row and the right class type.

    Code:
    SELECT
    	student.id,
    	student.name
    FROM
    	student
    	INNER JOIN classes ON (student.id = classes.student_fk)
    GROUP BY student.id
    HAVING COUNT(*) = 1 AND EVERY(classes.class_type = ...);
    Instead of "EVERY", you could also use "BOOL_OR". I don't know which one is faster in this case.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    6
    Rep Power
    0

    Thanks!


    Originally Posted by Jacques1
    Hi,

    Join both tables, group by the student ID and then select those students with exactly one row and the right class type.

    Code:
    SELECT
    	student.id,
    	student.name
    FROM
    	student
    	INNER JOIN classes ON (student.id = classes.student_fk)
    GROUP BY student.id
    HAVING COUNT(*) = 1 AND EVERY(classes.class_type = ...);
    Instead of "EVERY", you could also use "BOOL_OR". I don't know which one is faster in this case.

    Thanks for the speedy reply! If this works as expected I will respond back here to confirm.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    6
    Rep Power
    0

    worked great!


    Originally Posted by argo4242
    Thanks for the speedy reply! If this works as expected I will respond back here to confirm.
    It worked like a champ. thanks.

IMN logo majestic logo threadwatch logo seochat tools logo