Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2010
    Posts
    9

    help me modify sql query

    I'm back and need more help

    When I run this query
    Code:
    SELECT persons.personID, firstname, lastname, lecture, there 
    FROM persons, lectures, person_lectures
    WHERE  person_lectures.personID = persons.personID 
    		AND person_lectures.lectureID = lectures.lectureID
    		AND persons.personID = 1"
    I get this:

    Code:
    +----------+-----------+----------+---------+-------+
    | personID | firstname | lastname | lecture | there |
    +----------+-----------+----------+---------+-------+
    |    1	   |    Jack   |   Ryan   |    L1   |  YES  |
    +----------+-----------+----------+---------+-------+
    |    1	   |    Jack   |   Ryan   |    L2   |  YES  |
    +----------+-----------+----------+---------+-------+
    |    1	   |    Jack   |   Ryan   |    L3   |   NO  |
    +----------+-----------+----------+---------+-------+
    but what I really want is this
    Code:
    +----------+-----------+----------+---------+---------+---------+
    | personID | firstname | lastname |    L1   |    L2   |    L3   |
    +----------+-----------+----------+---------+---------+---------+
    |    1     |   Jack    |   Ryan   |   YES   |   YES   |    NO   |
    +----------+-----------+----------+---------+---------+---------+
    do you have any idea how to do this?

    Persons:

    Code:
    CREATE TABLE  persons
    ( personID TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY 
    , firstname VARCHAR( 30 ) NOT NULL 
    , lastname VARCHAR( 30 ) NOT NULL
    ) ENGINE = INNODB;
    Lectures:

    Code:
    CREATE TABLE  lectures 
    ( lectureID TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY 
    , lecture VARCHAR( 3 ) NOT NULL
    , there VARCHAR( 3 ) NOT NULL
    ) ENGINE = INNODB;
    PersonsLectures:

    Code:
    CREATE TABLE person_lectures 
    ( personID TINYINT NOT NULL
    , lectureID TINYINT NOT NULL 
    , PRIMARY KEY ( personID, lectureID )
    , INDEX person_lectures ( lectureID, personID )
    , FOREIGN KEY ( personID ) REFERENCES persons ( personID )
    , FOREIGN KEY ( lectureID ) REFERENCES lectures ( lectureID )
    ) ENGINE = INNODB;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT persons.personID
         , persons.firstname
         , persons.lastname
         , MAX(CASE WHEN lecture='L1' THEN there ELSE NULL END) AS L1
         , MAX(CASE WHEN lecture='L2' THEN there ELSE NULL END) AS L2
         , MAX(CASE WHEN lecture='L3' THEN there ELSE NULL END) AS L3
      FROM persons
    INNER
      JOIN person_lectures
        ON person_lectures.personID = persons.personID
    INNER
      JOIN lectures
        ON lectures.lectureID = person_lectures.lectureID 
     WHERE persons.personID = 1
    GROUP
        BY persons.personID
    notice that the GROUP BY clause has only 1 column, but the SELECT clause has 2 additional non-aggregate columns -- you are allowed to do this only in mysql, where it not only works but can be a bit more efficient than including the extra columns in the GROUP BY

    i knew you were using mysql from the AUTO_INCREMENT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2010
    Posts
    9
    You're right about MYSQL

    thanks a lot for your help
    Last edited by pootle flump; 06-14-10 at 06:36.

  4. #4
    Join Date
    Mar 2010
    Posts
    9
    I ran into a bit of a snag

    I want to include all of the persons, whether they were on a lecture or not, so when I tried to modify your line

    Code:
     WHERE persons.personID = 1
    and changed it into

    Code:
     WHERE persons.personID BETWEEN 1 AND 100
    I only got data for the persons whose presence on a lecture I explicitly set to "Yes" or "No". All the other persons who are in the table persons were not displayed, and I would want them too.

    Can you help me mend this?

    * I chose 100 because there will never be 100 persons in my table, but also because I didnt get any results with this line
    Code:
     WHERE persons.personID IN persons

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please do not slip links to nonsense in to your posts in technical threads.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If you want all people, remove the WHERE clause entirely.
    To have everyone display in the query irrespective of their attendance in lectures, change your join to LEFT OUTER JOINS.

  7. #7
    Join Date
    Mar 2010
    Posts
    9
    Sorry about the link to youtube, I was just overjoyed when I saw the solution.

    Thanks for the advice

Posting Permissions

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