SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    319
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question The old sub category query in one problem

    Table 1: Client
    id, name, email

    Table 2: Client_Tree
    id, parent_id, child_id

    What I'd like todo is have a single query that gets Client rows without a Client_Tree row or Client rows with a Client_Tree row but only parent_id as their Client.id

    I'd also like todo a count on that query for Client_Tree rows with them as the parent_id.

    So then, if the count is higher than one I can loop the results and bind the child rows.


    So far I have

    Code:
    SELECT DISTINCT d.*, COUNT(ct.id) as ct_count
    
    FROM Client as d 
    
    LEFT JOIN Client_Tree as ct ON (ct.parent_id = d.id)
    
    WHERE (ct.id IS NULL OR ct.parent_id = d.id)

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    No need for that WHERE, you already got only the rows where ct.id is NULL or ct.parentid = d.id

    Does this query give you what you need?
    If not, what's wrong with it?

  3. #3
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    319
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It only returns one row, which is the one in Client_Tree, I want Client results which arent in Client_Tree aswell.

    If I remove COUNT(ct.id) as ct_count from the field list I get a better result, but then I don't have any count for the children

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Get rid of the DISTINCT, and use a GROUP BY instead.
    And you should still get '1' as a result of the COUNT for rows that are not present in the Client_Tree table.

    If so, try this:
    Code:
    SELECT 
        d.*
      , SUM(CASE WHEN ct.id IS NULL THEN 0 ELSE 1 END) AS ct_count
    FROM Client as d 
    LEFT JOIN Client_Tree as ct 
    ON ct.parent_id = d.id
    GROUP BY d.id

  5. #5
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    319
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great! The only issue now is that the child row comes through in the results, aka a row in Client_Tree with child_id = d.id, means that "d" row shouldnt appear in the list as its going to be put under the parent client

    Another field sub query?

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Another left join of the Client_tree table, this time on child_id, and the result must be NULL (the client must not present in the client_tree table as a child):
    Code:
    SELECT 
        d.*
      , SUM(CASE WHEN ct.id IS NULL THEN 0 ELSE 1 END) AS ct_count
    FROM Client as d 
    LEFT JOIN Client_Tree as ct 
    ON ct.parent_id = d.id
    LEFT JOIN Client_Tree as ct2 
    ON ct2.child_id = d.id
    WHERE ct2.id IS NULL
    GROUP BY d.id

  7. #7
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    319
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks! Fantastic


Tags for this Thread

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
  •