Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2015
    Posts
    2

    Unanswered: [SOLVED] Select Query on multiple tables with count(*)

    Hello everybody,
    First post in this forum, maybe I can get help here! This problem is to complex for other forums... (but still not that complex ig)

    EDIT:
    Fixed the problem myself... after HOURS of trying
    Used the following horribly easy query:
    Code:
    select 
        classes.class_ID, 
        classes.description, 
        count(user_classes.user_ID_int_FK) as students
    from classes
    left join user_classes
    on user_classes.class_ID_FK = classes.class_ID
    group by class_ID
    Following Datase:
    Click image for larger version. 

Name:	Database.JPG 
Views:	2 
Size:	47.1 KB 
ID:	16111

    The List I want to get should look like this:
    Click image for larger version. 

Name:	Table.JPG 
Views:	1 
Size:	11.6 KB 
ID:	16112
    It shows all the classes with ID, name and how many students are in that class.

    Which it does already... I used the following query:
    Code:
    SELECT 
    	classes.class_ID, 
    	classes.description,
    	count(*) as students
    FROM
    	classes,
    	user_classes
    WHERE 
    	classes.class_ID = user_classes.class_ID_FK
    GROUP BY class_ID;
    There is one problem: I also want to get those classes that have 0 students participating as well in that list! But I dont get it to work cause of the "classes.class_ID = user_classes.class_ID_FK"-clause, which only works if there are students assigned to that class.

    So how do I do that? Does it require 2 Queries?? I really have no idea... googled it for a bit but couldnt get a result out of that!
    Last edited by jansch; 01-21-15 at 16:02.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Look at using LEFT JOIN syntax:

    Code:
    SELECT 
    	classes.class_ID, 
    	classes.description,
    	count(u.class_ID_FK) as students
    FROM classes c
    LEFT JOIN	user_classes u ON (c.class_ID = u.class_ID_FK)
    GROUP BY class_ID;
    Last edited by it-iss.com; 01-26-15 at 16:50.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

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
  •