Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2012
    Posts
    1

    Lightbulb Unanswered: How to - Complex MySQL Query

    Hi

    I'm having tremendous difficulty in generating a MySQL query results in several different tables.

    I need to join the result of 2 MySQL queries, but I don't know how to do that.

    The first query (that is working) is:

    Code:
    SELECT r.id,s.id,s.name,c.id,c.name,c.level FROM registration r, student s, class c WHERE s.id=r.studentid AND c.id=r.classid
    The second one, "take" several information from other tables, and I need those information to be "joined" to the previous query. The problem is that I don't know how to relate the variables classid e studentid needed to get the right data of the users that are being listed in the first query.

    The second query (that is working too, but separately from the first one) looks like this:

    Code:
    SELECT payment,presence,homework,grade,SUM(100+(payment*(-10))+(presence*(-1))+(homework*(-2))+grade) AS ranking
    FROM
    (SELECT COUNT(*) AS payment FROM payment WHERE overdue=2 AND studentid=$studentid AND classid=$classid) AS p,
    (SELECT COUNT(*) AS presence FROM calllistentries WHERE presence=0 AND studentid=$studentid AND classid=$classid) AS f,
    (SELECT COUNT(*) AS homework FROM calllistentries WHERE homework=0 AND studentid=$studentid AND classid=$classid) AS h,
    (SELECT SUM(exam1 + exam2 + exam3 + behavior1 + behavior2 + behavior3 + behavior4 + behavior5 + behavior6) AS grade FROM grade WHERE studentid=$studentid AND classid=$classid) AS g
    I need both results to be in the same query because I'll need to ORDER BY ranking, that is generated in the second part.

    Please Help! Thanks!

    Here you can download the MySQL Dump of my DB tables: http://www.sitenamedida.com/mysql_dump.sql
    Last edited by andre.maito; 03-09-12 at 13:33.

  2. #2
    Join Date
    Jan 2012
    Posts
    84
    My guess is that you need something like this:
    Code:
    SELECT  some_alias.*,
            100+(payment*(-10))+(presence*(-1))+(homework*(-2))+grade AS ranking
    FROM (
        SELECT r.id registration_id,
               s.id student_id,
               s.name student_name,
               c.id class_id,
               c.name class_name,
               c.level,
            ( SELECT COUNT(*) AS payment FROM payment 
              WHERE overdue=2 AND studentid=s.id AND classid=c.id) AS payment,
            ( SELECT COUNT(*) AS presence FROM calllistentries 
              WHERE presence=0 AND studentid=s.id AND classid=c.id) AS presence,
            ( SELECT COUNT(*) AS homework FROM calllistentries 
              WHERE homework=0 AND studentid=s.id AND classid=c.id) AS homework,
            ( SELECT SUM(exam1 + exam2 + exam3 + behavior1 + behavior2 + behavior3 + behavior4 + behavior5 + behavior6) AS grade 
              FROM grade WHERE studentid=s.id AND classid=c.id) AS grade
        FROM registration r, student s, class c 
        WHERE s.id=r.studentid AND c.id=r.classid
    ) some_alias
    ;

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
  •