Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Senior Coder
    Join Date
    Aug 2002
    Posts
    3,467
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can't find the right query....

    Ok, here's a snapshot of my data
    PHP Code:
    users
    +----+------------+
    id Type       |
    +----+------------+
    1  Apprentice |
    2  LANWizard  |
    3  Admin      |
    4  Apprentice |
    +----+------------+

    todo
    +----+-----+--------+
    id uid apprID |
    +----+-----+--------+
    1  3   1      |
    2  2   1      |
    3  2   0      | <-- Unassigned todo item
    +----+-----+--------+ 
    Ok, todo.uid is the ID number of the user that the todo item belongs to. todo.apprID is the ID number of the Apprentice that the todo item has been assigned to. I'd like to get the following result from 1 query
    PHP Code:
    +--------+--------------+
    apprID num_of_todos |
    +--------+--------------+
    1      2            |
    4      0            |
    +--------+--------------+ 
    But I can't seem to conjure up the correct syntax to get this done with just 1 query. As you can see I'm using the defualt of 0 for a todo item that hasn't been assigned to an Apprentice. Should this column accept NULL instead?

    Follow up:
    I've been working on this and here are some results I'm getting
    PHP Code:
    SELECT u.id as apprIDcount(apprID) as num_of_todos
    FROM users u
    todo t
    WHERE u
    .Type 'Apprentice' and apprID != 0
    GROUP BY u
    .id

    result
    +--------+--------------+
    apprID num_of_todos |
    +--------+--------------+
    1      2            |
    +--------+--------------+

    SELECT u.id as apprIDcount(apprID) as num_of_todos
    FROM users u
    todo t
    WHERE u
    .Type 'Apprentice'
    GROUP BY u.id

    results
    +--------+--------------+
    apprID num_of_todos |
    +--------+--------------+
    1      3            |
    4      3            |
    +--------+--------------+

    SELECT u.id as apprIDcount(apprID) as num_of_todos
    FROM users u
    todo t
    WHERE u
    .id t.apprID
    GROUP BY u
    .id

    result
    +--------+--------------+
    apprID num_of_todos |
    +--------+--------------+
    1      2            |
    +--------+--------------+ 
    My Site | fValidate | My Brainbench | MSDN | Gecko | xBrowser DOM | PHP | Ars | PVP
    “Minds are like parachutes. They don't work unless they are open”
    “Maturity is simply knowing when to not be immature”

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    frankfurt, german banana republic
    Posts
    1,848
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I believe that in this particular case you need LEFT JOINs to get also those values listed that don't have a matching ON condition with the joined table.

    PHP Code:
    SELECT

    users
    .id AS apprID,
    COUNT(todo.apprID) AS num_of_todos

    FROM users
    LEFT JOIN todo ON users
    .id todo.apprID

    WHERE users
    .type 'Apprentice'

    GROUP BY todo.apprID

    ORDER BY users
    .id ASC
    gave on my machine with two tables set up exactly like your test data the result

    PHP Code:
    +--------+--------------+

    apprID num_of_todos |

    +--------+--------------+

    1      2            |

    4      0            |

    +--------+--------------+ 
    and also scaled well for more users + correlating todo entries.

    You might double check the SQL, left joins aren't really my strength, I'll still find them occasionally quite daunting... as in this case. But not impossible.

  • #3
    Senior Coder
    Join Date
    Aug 2002
    Posts
    3,467
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Cheers mordred. I acutally got this figured out a bit ago, but it's good to have some confirmation that I got it right. It's good to know this will scale well, because there will eventually be 3000+ users each with an indefinite number of customers, and an undetermined amount of apprentices, all of which can use the todo list functions.

    My Site | fValidate | My Brainbench | MSDN | Gecko | xBrowser DOM | PHP | Ars | PVP
    “Minds are like parachutes. They don't work unless they are open”
    “Maturity is simply knowing when to not be immature”


  •  

    Posting Permissions

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