SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    LEFT OUTER JOIN with GROUP BY? Wrong rows showing up.

    Hi. This is my first post. I have a little problem with using LEFT OUTER JOIN with GROUP BY.

    I have two tables, customer and projects. Each customer can have many projects. I want to display the customers regardless of 0 projects and display the number of projects next to each row of customers. I have:

    Customer 1 = 20 projects with this foreign key
    Customer 2 = 0 projects with this foreign key
    Customer 3 = 0 projects with this foreign key
    Customer 4 = 0 projects with this foreign key

    Code:
    SELECT c.*, date_format(c.date_added, '%D %M %Y') as c_date_added, count(p.c_id) as project_count, 0 as time, c.c_id as cust_id
    FROM (`customers` c)
      LEFT OUTER JOIN `projects` p ON `c`.`c_id` = `p`.`c_id`
    GROUP BY `p`.`c_id` LIMIT 10;
    This query produces two rows, customer 1 and customer 2. Where are customers 3 and 4? Only customer one has a foreign key match inside projects table so it is no a case of not joining.

    Here is sql if you want it:

    Code:
    CREATE TABLE `customers` (
      `c_id` int(11) NOT NULL auto_increment,
      `cust_name` varchar(100) default NULL,
      `description` text,
      `date_added` datetime default NULL,
      PRIMARY KEY  (`c_id`)
    )
    
    CREATE TABLE `projects` (
      `p_id` int(11) NOT NULL auto_increment,
      `c_id` int(11) default NULL,
      `project_name` varchar(100) default NULL,
      `project_desc` text,
      `date_added` datetime default NULL,
      `parent_id` int(11) default NULL,
      PRIMARY KEY  (`p_id`)
    )

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Adamantus View Post
    This query produces two rows, customer 1 and customer 2. Where are customers 3 and 4?
    your query is fine

    there must be something else going on that you haven't revealed

    are you sure there is no WHERE clause?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    your query is fine

    there must be something else going on that you haven't revealed

    are you sure there is no WHERE clause?
    I did change it for simplicity so you could read through it easily. The complete query is this but with / without the where claus is the same result:

    Code:
    SELECT *, date_format(c.date_added, '%D %M %Y') as c_date_added, count(p.c_id) as project_count, 0 as time, c.c_id as cust_id
    FROM (`customers` c)
      LEFT OUTER JOIN `projects` p ON `c`.`c_id` = `p`.`c_id`
    WHERE c.c_id!=1
    GROUP BY `p`.`c_id`
    LIMIT 10
    I definitely have 4 customers. Only one has a link inside projects (customer 1). For some reason customer 2 comes back, then no others.

    P.s. Thanks for replying.

  4. #4
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    696
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    group 
        by c.c_id
    Since p.c_id is null for three records this is considered as one group.

  5. #5
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by swampBoogie View Post
    Code:
    group 
        by c.c_id
    Since p.c_id is null for three records this is considered as one group.
    That was it, thank you swampBoogie! It was the fact that I was just looking at the name column. The other 3 rows must have condensed into one and just given it the field names of the first, making it look as though customers 3 and 4 where missing. This will seriously help this and future projects, I've been scared of left/right joins for years.

    SOLUTION
    So the solution is that tables with a left join and group by will group the null rows into one row. So alter the group by to refer to the LEFT joined or the RIGHT joined table.


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
  •