Thread: Help with query

    #1
  1. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,034
    Rep Power
    535

    Help with query


    I've been struggling with a query, and hoping someone can help. I have the following tables.
    Code:
    contacts
    -id (pk)
    -data
    
    projects
    -id (pk)
    -data
    
    bid_contacts
    -contacts_id (pk)
    -projects_id (pk)
    -data
    
    bidlog
    -id (pk)
    -bid_contacts_contacts_id (fk)
    -bid_contacts_projects_id (fk)
    For a given contacts_id and projects_id, I can retrieve some data from contacts and bid_contacts as such:
    Code:
    SELECT c.data, bc.data
    FROM contacts AS c
    LEFT OUTER JOIN bid_contacts AS bc ON bc.contacts_id=c.id AND bc.projects_id=321
    WHERE c.id=123;
    Now, I want to retrieve the same data, except also retrieve the number of times bidlog is joined to bid_contacts. I've tried the following with no success.
    Code:
    SELECT c.data, bc.data, bc.bidlog_count
    FROM contacts AS c
    LEFT OUTER JOIN 
    (
        SELECT COUNT(bl.id) AS bidlog_count, bc.data,bc.contacts_id,bc.projects_id
        FROM bid_contacts AS bc
        LEFT OUTER JOIN bid_log AS bl ON bl.bid_contacts_contacts_id=bc.contacts_id AND bl.bid_contacts_projects_id=bc.projects_id
        GROUP BY contacts_id
    ) AS bc ON bc.contacts_id=c.id AND bc.projects_id=321
    WHERE c.id=123';
    Any suggestions would be appreciated. Thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,239
    Rep Power
    4279
    Originally Posted by NotionCommotion
    I've tried the following with no success.
    sorry, i am not familiar with the "no success" error message
    Code:
    SELECT c.data
         , bc.data
         , bl.logs
      FROM contacts AS c
    LEFT OUTER 
      JOIN bid_contacts AS bc 
        ON bc.contacts_id = c.id 
       AND bc.projects_id = 321
    LEFT OUTER
      JOIN ( SELECT bid_contacts_contacts_id
                  , COUNT(*) as logs
               FROM bid_log  
              WHERE bid_contacts_projects_id = 321
             GROUP 
                 BY bid_contacts_contacts_id ) AS bl
        ON bl.bid_contacts_contacts_id = c.id          
     WHERE c.id = 123

    Comments on this post

    • NotionCommotion agrees : Works like a charm. Thanks!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,034
    Rep Power
    535
    Actually, one small snag.

    bl.logs returns NULL, not zero when there are no results.

    Should I use COALESCE(), or is there a better option?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,239
    Rep Power
    4279
    Originally Posted by NotionCommotion
    Should I use COALESCE
    yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo