SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Thread: Union Help

  1. #1
    SitePoint Enthusiast justinpalmer's Avatar
    Join Date
    Oct 2005
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Union Help

    Hi,

    I have the following tables, query and result. I seem to be getting double rows when what I want is to have one row per record with the correct amounts (obviously):

    TABLES:
    -----------------------------------------------
    --
    -- Table structure for table `users_core`
    --

    CREATE TABLE `users_core` (
    `id` int(11) NOT NULL auto_increment,
    `USER_ID` int(11) NOT NULL default '0',
    `first_name` varchar(255) NOT NULL default '',
    `middle_name` varchar(255) NOT NULL default '',
    `last_name` varchar(255) NOT NULL default ''
    PRIMARY KEY (`id`),
    KEY `USER_ID` (`USER_ID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    -- -----------------------------------------------------------------------
    -- Table structure for table `student_placement`
    --

    CREATE TABLE `student_placement` (
    `id` int(11) NOT NULL auto_increment,
    `STUDENT_ID` int(11) NOT NULL default '0',
    `USER_ID` int(11) NOT NULL default '0',
    `placement` char(1) NOT NULL default '0',
    `placement_date` bigint(20) NOT NULL default '0',
    `approved` char(1) NOT NULL default '0'
    PRIMARY KEY (`id`),
    UNIQUE KEY `STUDENT_ID` (`STUDENT_ID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1

    -------------------------------------------------------------------
    -- Table structure for table `profile_core`
    --

    CREATE TABLE `profile_core` (
    `STUDENT_ID` int(11) NOT NULL auto_increment,
    `SUPERVISOR_USER_ID` int(11) NOT NULL default '0',
    `contract` year(4) NOT NULL default '0000'
    PRIMARY KEY (`STUDENT_ID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1


    QUERY:
    -----------------------------------------------

    Code:
    (
    SELECT uCORE.USER_ID, 
        CONCAT( uCORE.last_name, ', ', uCORE.first_name ) AS User, 
        CONCAT( uREGIONAL.last_name, ', ', uREGIONAL.first_name ) AS RegionalLeader, 
        CONCAT( uTEAMLEADER.last_name, ', ', uTEAMLEADER.first_name ) AS TeamLeader, 
        COUNT( sPLACEMENT.STUDENT_ID ) AS PlacementCount, 
        COUNT( sSUPERVISION.STUDENT_ID ) AS SupervisionCount
    FROM `student_placement` AS sPLACEMENT
        INNER JOIN `profile_core` AS sSUPERVISION 
            ON sPLACEMENT.STUDENT_ID = sSUPERVISION.STUDENT_ID
        INNER JOIN `users_core` AS uCORE 
            ON sSUPERVISION.SUPERVISOR_USER_ID = uCORE.USER_ID
        LEFT JOIN `users` AS uUSER 
            ON uCORE.USER_ID = uUSER.USER_ID
        LEFT JOIN `users_core` AS uREGIONAL 
            ON uUSER.REGIONAL_ID = uREGIONAL.USER_ID
        LEFT JOIN `users_core` AS uTEAMLEADER 
            ON uUSER.TEAM_LEADER_ID = uTEAMLEADER.USER_ID
    WHERE 
            sSUPERVISION.contract =2007
        AND uUSER.status =1
        AND (
            uUSER.REGIONAL_ID =23
        OR uUSER.TEAM_LEADER_ID =23
            )
        AND sPLACEMENT.approved =1
    GROUP BY uUSER.USER_ID, User, RegionalLeader, TeamLeader
    )
    UNION (
    
    SELECT uCORE.USER_ID, 
        CONCAT( uCORE.last_name, ', ', uCORE.first_name ) AS User, 
        CONCAT( uREGIONAL.last_name, ', ', uREGIONAL.first_name ) AS RegionalLeader, 
        CONCAT( uTEAMLEADER.last_name, ', ', uTEAMLEADER.first_name ) AS TeamLeader, 
        COUNT( sPLACEMENT.STUDENT_ID ) AS PlacementCount, 
        COUNT( sSUPERVISION.STUDENT_ID ) AS SupervisionCount
    FROM `student_placement` AS sPLACEMENT
        INNER JOIN `profile_core` AS sSUPERVISION 
            ON sPLACEMENT.STUDENT_ID = sSUPERVISION.STUDENT_ID
        INNER JOIN `users_core` AS uCORE 
            ON sPLACEMENT.USER_ID = uCORE.USER_ID
        LEFT JOIN `users` AS uUSER 
            ON uCORE.USER_ID = uUSER.USER_ID
        LEFT JOIN `users_core` AS uREGIONAL 
            ON uUSER.REGIONAL_ID = uREGIONAL.USER_ID
        LEFT JOIN `users_core` AS uTEAMLEADER 
            ON uUSER.TEAM_LEADER_ID = uTEAMLEADER.USER_ID
    WHERE 
          sPLACEMENT.approved =1
    AND sSUPERVISION.contract =2007
    AND uUSER.status =1
    AND (
           uUSER.REGIONAL_ID =23
    OR     uUSER.TEAM_LEADER_ID =23
            )
    GROUP BY uUSER.USER_ID, User, RegionalLeader, TeamLeader
    )
    ORDER BY User
    RESULT FOR THIS QUERY:
    ----------------------------------------------------
    USER_ID User RegionalLeader TeamLeader PlacementCount SupervisionCount
    103 Andersen, Jim Drake, Nate NULL 1 1
    103 Andersen, Jim Drake, Nate NULL 2 2

    93 Blaine, Betty Drake, Nate Dahn, Yawn 1 1


    Whenever the count is different for Placement and Supervision it has two rows like the bolded results above. For Andersen, Jim it should be 2 Placements and 1 Supervision.

    Any hints are greatly appreciated.

    Many thanks,
    Justin Palmer
    MySQL v.5

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    to find out where the dupes are coming from, drop the UNION and run one SELECT at a time, remove the GROUP BYs, and add relevant keys to the SELECT clauses so that you can see which rows are joining to which rows

    you must have a one-to-many relationship somewhere along the line of all those joins
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast justinpalmer's Avatar
    Join Date
    Oct 2005
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937.

    I will try to do this and post some results.

    Regards,
    Justin Palmer
    MySQL v.5

  4. #4
    SitePoint Enthusiast justinpalmer's Avatar
    Join Date
    Oct 2005
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Yes, I do have a one to many relationship. I want to count the number of approved placements and the number of supervisions for each user ( `users_core` table - USER_ID).

    Placements are kept track of in the `student_placement` table (USER_ID) and supervisions are kept in the `profile_core` table (SUPERVISOR_USER_ID).

    I can run the queries seperate and get the results I want:

    PLACEMENT COUNT:
    ----------------------
    Code:
    SELECT 
        uCORE.USER_ID, 
        CONCAT( uCORE.last_name, ', ', uCORE.first_name ) 
            AS User, 
        COUNT( sPLACEMENT.STUDENT_ID ) 
            AS Count
    FROM `student_placement` AS sPLACEMENT
        LEFT 
            JOIN `profile_core` AS sSUPERVISION 
                ON sPLACEMENT.STUDENT_ID = sSUPERVISION.STUDENT_ID
        LEFT 
            JOIN `users_core` AS uCORE 
                ON sPLACEMENT.USER_ID = uCORE.USER_ID
        LEFT 
            JOIN `users` AS uUSER 
                ON uCORE.USER_ID = uUSER.USER_ID
    WHERE 
            sSUPERVISION.contract =2007
    AND   uUSER.status =1
    AND (
            uUSER.REGIONAL_ID =23
    OR     uUSER.TEAM_LEADER_ID =23
            )
    AND sPLACEMENT.approved =1
    GROUP BY uUSER.USER_ID, User
    SUPERVISION COUNT:
    ------------------------------------
    Code:
    SELECT 
        uCORE.USER_ID, 
        CONCAT( uCORE.last_name, ', ', uCORE.first_name ) 
            AS User, 
        COUNT( sSUPERVISION.STUDENT_ID ) 
            AS Count
    FROM `student_placement` AS sPLACEMENT
        LEFT 
            JOIN `profile_core` AS sSUPERVISION 
                ON sPLACEMENT.STUDENT_ID = sSUPERVISION.STUDENT_ID
        LEFT 
            JOIN `users_core` AS uCORE 
                ON sSUPERVISION.SUPERVISOR_USER_ID = uCORE.USER_ID
        LEFT 
            JOIN `users` AS uUSER 
                ON uCORE.USER_ID = uUSER.USER_ID
    WHERE 
            sSUPERVISION.contract =2007
    AND   uUSER.status =1
    AND (
            uUSER.REGIONAL_ID =23
    OR     uUSER.TEAM_LEADER_ID =23
            )
    AND sPLACEMENT.approved =1
    GROUP BY uUSER.USER_ID, User
    In the above queries I have bolded the only items that change to calculate the Placement Count and the Supervision Count. It is how the user is joined to the different counts:

    Placement:
    Code:
    LEFT 
            JOIN `users_core` AS uCORE 
                ON sPLACEMENT.USER_ID = uCORE.USER_ID
    Supervision:
    Code:
    LEFT 
            JOIN `users_core` AS uCORE 
                ON sSUPERVISION.SUPERVISOR_USER_ID = uCORE.USER_ID
    To calculate them each individually I still need to group.

    Any hints are greatly appreciated.

    Kind regards,
    Justin Palmer
    MySQL v.5

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you said both those individual queries are producing the correct results?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast justinpalmer's Avatar
    Join Date
    Oct 2005
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you said both those individual queries are producing the correct results?
    Hi,

    Yep, sure are.

    Many thanks,
    Justin Palmer
    MySQL v.5

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i've tried to look at this a couple of times and kept getting confused

    where did the 4th table come from, and what does it look like?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast justinpalmer's Avatar
    Join Date
    Oct 2005
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face

    Quote Originally Posted by r937 View Post
    i've tried to look at this a couple of times and kept getting confused

    where did the 4th table come from, and what does it look like?
    Hi,

    I apologize. Here it is:

    --
    -- Table structure for table `users`
    --

    CREATE TABLE `users` (
    `USER_ID` int(11) NOT NULL auto_increment,
    `REGIONAL_ID` int(11) NOT NULL default '0',
    `TEAM_LEADER_ID` int(11) NOT NULL default '0',
    `username` varchar(255) NOT NULL default '',
    `password` varchar(255) NOT NULL default '',
    `access_level` smallint(2) NOT NULL default '0',
    `status` tinyint(1) NOT NULL default '1',
    `last_accessed` bigint(20) NOT NULL default '0',
    `last_modified` bigint(20) NOT NULL default '0',
    `created` bigint(20) NOT NULL default '0',
    PRIMARY KEY (`USER_ID`),
    UNIQUE KEY `username` (`username`)
    ) ENGINE=MyISAM DEFAULT;

    I basically only reference that table to make sure that the user has the correct team or regional leader and that they are an enabled user.

    I hope that helps you, help me....

    Kind regards,

    Justin Palmer
    Justin Palmer
    MySQL v.5

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    do you still require a solution for this?

    sorry, i took a bit of a break

    i think i can do this in a single query, if you still need it, but it would take a bit of work, so i thought i'd ask first
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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