#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. Banned
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,938
    Rep Power
    0
    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