SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Categories/subcategories question

    I'm currently recreating a table in my database that has a category/subcategory relationship. The categories are offices, and within each office is one or more team (the "subcategories"). Each team contains lots of people, and the page I'm building is basically an office directory.

    Currently I've structured the table like this:

    team_id (numeric)
    team_name (text)
    team_desc (text)
    parent_team (numeric)

    I have the following query:

    Code SQL:
    SELECT    offices.team_name AS office
            , teams.team_name AS team
    FROM hep_teams AS offices
    INNER JOIN hep_teams AS teams
            ON teams.parent_team = offices.team_id
    ORDER BY offices.team_name

    Which gives me a nice bulleted list:

    • Office1
      • Team11
      • Team12
      • Team13

    • Office2
      • Team21
      • Team22
      • Team23

    • Office3
      • Team31
      • Team32


    Now, my problem comes when I have to join my staff list. Each staff member is a member of one of these teams. But when I join my staff table in, I'm not getting all of my people. It appears that I have to join my staff table twice - once to the teams table on its "office" alias, and again to the teams table as its "teams" alias. This is quite clunky.

    I think my stumbling block is that everyone in the office is a member of a team EXCEPT the big boss and her two assistants, who are members of Office 1 but not any team. I think this is why I'm ending up having to join the staff table twice. Unless of course, there is a better way that I'm missing.
    <cfset myblog = "http://cydewaze.org/">

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    I think this is why I'm ending up having to join the staff table twice.
    nailed it right there
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    nailed it right there
    Ok, so, I've done something like this, with not great results. I'm getting staff appearing where I don't want them.

    Code SQL:
    SELECT    offices.team_name AS office
            , teams.team_name AS team
            , staff.fullname AS fullname1
            , staff2.fullname AS fullname2
    FROM hep_teams AS offices
    INNER JOIN hep_teams AS teams
            ON teams.parent_team = offices.team_id
    LEFT JOIN staff
            ON staff.team = hep_teams.team_name
    LEFT JOIN staff AS staff2
            ON staff2.team = hep_teams.team_name
    ORDER BY offices.team_name

    What I'm looking for is this:

    • Office1
      • Bill Smith - Office Director
      • Jane Doe - Admin assistant
      • Team 11
        • Bob Clark - Team Leader
        • Sam Spade - Team Member
        • Joe Jones - Team Member

      • Team 12
        • Mike Johnson - Team Leader
        • Gail Reed - Team Member
        • Mark Lane - Team Member


    • Office 2
      • Jill Hoff - Office Director
      • Sam Walker - Admin assistant
      • Team 21
        • Steve Schneider - Team Leader
        • Bruce James - Team Member
        • Silvia Wu - Team Member

      • Team 22
        • Pat Cumins - Team Leader
        • Sue Sheperd - Team Member
        • Linda Plant - Team Member




    And what I keep getting is this:

    • Office1
      • Bill Smith - Office Director
      • Jane Doe - Admin assistant
      • Bob Clark - Team Leader
      • Sam Spade - Team Member
      • Joe Jones - Team Member
      • Mike Johnson - Team Leader
      • Gail Reed - Team Member
      • Mark Lane - Team Member
      • Team 11
        • Bill Smith - Office Director
        • Jane Doe - Admin assistant
        • Bob Clark - Team Leader
        • Sam Spade - Team Member
        • Joe Jones - Team Member
        • Mike Johnson - Team Leader
        • Gail Reed - Team Member
        • Mark Lane - Team Member

      • Team 12
        • Bill Smith - Office Director
        • Jane Doe - Admin assistant
        • Bob Clark - Team Leader
        • Sam Spade - Team Member
        • Joe Jones - Team Member
        • Mike Johnson - Team Leader
        • Gail Reed - Team Member
        • Mark Lane - Team Member


    • Office 2


    [snip]

    I'm obviously tripping up somewhere.
    <cfset myblog = "http://cydewaze.org/">

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i could've warned you about that, i guess

    what you need is a UNION

    one SELECT for the offices, plus staff if any

    another SELECT for the teams, plus staff if any

    ORDER BY on the UNION will ensure the rows get interleaved properly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    OOOH! I actually had started to try that, but figured that if I thought of it, it couldn't be right, so I stopped, hah!
    <cfset myblog = "http://cydewaze.org/">

  6. #6
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    OK, so... I have a UNION set up, but bear with me, because this is my first UNION with multiple JOINS, so I'm still a bit green.

    First off, it looks like both SELECTs have to be pretty much the same SELECT, because otherwise I get an error about an uneven number of columns, but one of the JOINs is slightly different. Second, it looks like the ORDER BY clause has to be on the first SELECT.

    Code SQL:
    SELECT    offices.team_name AS office
            , teams.team_name AS team
            , staff.fullname AS fullname
    FROM hep_teams AS offices
    INNER JOIN hep_teams AS teams
            ON teams.parent_team = offices.team_id
    INNER JOIN staff
            ON staff.team = offices.team_name
    ORDER BY staff.rank, offices.team_name
    UNION
    SELECT    offices.team_name AS office
            , teams.team_name AS team
            , staff.fullname AS fullname
    FROM hep_teams AS offices
    INNER JOIN hep_teams AS teams
            ON teams.parent_team = offices.team_id
    INNER JOIN staff
            ON staff.team = teams.team_name

    Now I have everyone showing up, and mostly in the proper place, except for the team leaders and assistants (first SELECT), who are showing up under a team, when they're not listed under a team. When I cfdump the query, sure enough, they have a populated team column, when in fact they're not a member of any team. I'm not sure what's doing that.

    It's also ignoring my rank ordering, making the admin assistants appear above the bosses (eek!)
    <cfset myblog = "http://cydewaze.org/">

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    OK, so... I have a UNION set up, but bear with me, because this is my first UNION with multiple JOINS, so I'm still a bit green.
    not a problem, sir

    Quote Originally Posted by cydewaze View Post
    First off, it looks like both SELECTs have to be pretty much the same SELECT, because otherwise I get an error about an uneven number of columns
    this makes sense when you think about it -- both SELECTs have to feed rows into the same result set

    the secret is to use "placeholder" columns to make the number equal


    Quote Originally Posted by cydewaze View Post
    Second, it looks like the ORDER BY clause has to be on the first SELECT.
    nope, a UNION is allowed to have only one ORDER BY and it goes at the very end (i'm suprised you didn't get a syntax error)

    try this --
    Code:
    SELECT offices.team_name AS office
         , teams.team_name AS team
         , staff.fullname AS fullname
         , staff.rank
      FROM hep_teams AS offices
    INNER 
      JOIN hep_teams AS teams
        ON teams.parent_team = offices.team_id
    INNER 
      JOIN staff
        ON staff.team = teams.team_name
    UNION ALL
    SELECT offices.team_name AS office
         , NULL AS team
         , staff.fullname AS fullname
         , staff.rank
      FROM hep_teams AS offices
    INNER 
      JOIN staff
        ON staff.team = offices.team_name
    ORDER
        BY office
         , team
         , rank
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    nope, a UNION is allowed to have only one ORDER BY and it goes at the very end (i'm suprised you didn't get a syntax error)
    It was probably some silly Access idiosyncrasy. Putting the ORDER BY at the end gave me this error:

    Only those fields requested in the first query can be included in an ORDER BY expression
    I'm always having ORDER BY problems. It seems like sometimes I am allowed to order by a column alias, and other times I'm not.

    Your new version (of COURSE) works perfectly (once I added the parens), but don't expect me not to inspect it to see where I went wrong with mine!

    Now, I just have to show my results in a nice way, because the unordered list method we have now is a bit ugly.
    <cfset myblog = "http://cydewaze.org/">

  9. #9
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    HMMM! I think you may have thrown me a curveball there, but don't you worry, I'll figure it out!
    <cfset myblog = "http://cydewaze.org/">

  10. #10
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    So, it would seem that my problem stems from the fact that my UNION sees the same rows in two different ways. I have one set (from the first SELECT) with a team name, and another result (from the second SELECT) with null for the team name. The UNION sees these as two different values, so it's displaying them both, so therefore I'm getting duplicates of all the staff members who are members of a team (but not the bosses).

    Removing the dupes is proving errrr, challenging.
    <cfset myblog = "http://cydewaze.org/">

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the query i gave you, if i did it right, pulls staff connected to teams (first select, two joins), and then staff connected to offices (second select, only one join)

    please confirm that you understand how my union query works

    if you're still getting dupes, perhaps you actually entered the staff under both offices and teams?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I'm pretty sure I do.

    When I run the top half of the query alone, I get everyone who is assigned to a team, and I don't get any of the bosses who are not members of a team.

    When I run the bottom half alone, I get everyone, with a NULL as the team, but the office is there. I think what I need is a way for the second half of the query to show only the people who are not team members to show up, rather than everyone. I can't use WHERE team IS NULL because that would hide everyone, since I assigned NULL to all the teams.


    The staff table goes is like this:

    name
    phone#
    email
    location
    rank
    team (this is what gets JOINED to the teams table).

    The teams table is:

    team_id (numeric)
    team_name (text)
    team_desc (text)
    parent_team (numeric)

    It's joined on staff.team = teams.team_name, so there aren't any duplicate assignments. Everyone is in the staff table only once.

    If parent_team is null, that person is a boss. Otherwise they're a team member. I've tried variations on WHERE statements using that, but haven't had any luck.
    <cfset myblog = "http://cydewaze.org/">

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, i think i see the problem

    i based my query on the query you gave in post #6, in which you incorrectly joined the staff using the team name

    try this correction --
    Code:
    SELECT offices.team_name AS office
         , teams.team_name AS team
         , staff.name
         , staff.rank
      FROM hep_teams AS offices
    INNER 
      JOIN hep_teams AS teams
        ON teams.parent_team = offices.team_id
    INNER 
      JOIN staff
        ON staff.team = teams.team_id
    UNION ALL
    SELECT offices.team_name AS office
         , NULL AS team
         , staff.name
         , staff.rank
      FROM hep_teams AS offices
    INNER 
      JOIN staff
        ON staff.team = offices.team_id
    ORDER
        BY office
         , team
         , rank
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    okay, i think i see the problem

    i based my query on the query you gave in post #6, in which you incorrectly joined the staff using the team name
    Well it actually does get joined on the team_name field.

    The team field in the staff database is something like OBPM-30. It's this way because I almost never have to show any of the other info from the teams table. So it's joined on the text fields staff.team = teams.team_name

    So your original query should have been right.

    The team_id is only used to identify the parents teams.

    What's happening is this (I'll use me as an example):

    name | office | team

    row 5: Paul | My Office Name | My Team Name
    row 11: Paul | My Team Name | Null

    Row 5 is correct. Row 11 has my team name in the office code column, and the team name column is null.
    <cfset myblog = "http://cydewaze.org/">

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    Row 5 is correct. Row 11 has my team name in the office code column, and the team name column is null.
    this makes no sense at all

    the columns in the query i gave you are: office, team, name, rank

    you're obviously running some other query (or else CFOUTPUTting them incorrectly) if you're getting name, office, team
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    this makes no sense at all
    Actually, it makes perfect sense!

    I went through the query line-by-line, and here's what I found:

    Code:
    SELECT offices.team_name AS office
         , teams.team_name AS team
         , staff.name
         , staff.rank
      FROM hep_teams AS offices
    INNER 
      JOIN hep_teams AS teams
        ON teams.parent_team = offices.team_id
    INNER 
      JOIN staff
        ON staff.team = teams.team_id
    UNION ALL
    SELECT offices.team_name AS office
         , NULL AS team
         , staff.name
         , staff.rank
      FROM hep_teams AS offices
    INNER 
      JOIN staff
        ON staff.team = offices.team_id
    ORDER
        BY office
         , team
         , rank

    My team is OBPM-30 and my office is OBPM-1. In the above line, we're selecting my TEAM NAME as office, so of course I'm there twice!

    I made a slight change:

    Code:
    SELECT offices.team_name AS office
         , teams.team_name AS team
         , staff.name
         , staff.rank
      FROM hep_teams AS offices
    INNER 
      JOIN hep_teams AS teams
        ON teams.parent_team = offices.team_id
    INNER 
      JOIN staff
        ON staff.team = teams.team_id
    UNION ALL
    SELECT NULL AS office
         , offices.team_name AS team
         , staff.name
         , staff.rank
      FROM hep_teams AS offices
    INNER 
      JOIN staff
        ON staff.team = offices.team_id
    ORDER
        BY office
         , team
         , rank
    And this fixed everything!

    EDIT: Actually, it fixed everything in the CFDUMP, and for around 10 minutes. I added some more columns to get people's job titles, and the stupid dupes are back. At least it felt good to think I solved it for 10 minutes.
    <cfset myblog = "http://cydewaze.org/">

  17. #17
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Ok, getting closer!

    Code:
    SELECT offices.team_name AS office
         , teams.team_name AS team
         , staff.name
         , staff.rank
      FROM hep_teams AS offices
    INNER 
      JOIN hep_teams AS teams
        ON teams.parent_team = offices.team_id
    INNER 
      JOIN staff
        ON staff.team = teams.team_id
    UNION ALL
    SELECT NULL AS office
         , offices.team_name AS team
         , staff.name
         , staff.rank
      FROM hep_teams AS offices
    INNER 
      JOIN staff
        ON staff.team = offices.team_id
    WHERE parent_team IS NULL
    ORDER
        BY office
         , team
         , rank
    Dupes are gone. Now I just need to get the order right.

    EDIT: Once I ordered it properly (there was a "team_order" column in the teams table) everything works (FINALLY!).

    Thanks for the help Rudy.
    <cfset myblog = "http://cydewaze.org/">

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    Code:
    WHERE parent_team IS NULL
    oh . my . #deity#

    i can't believe i overlooked this for so long

    my apologies, i should've had that in my query from the beginning
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    It's totally ok! I learned quite a lot from the omission! (in fact, I'm still not totally sure you didn't leave it out on purpose)
    <cfset myblog = "http://cydewaze.org/">


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
  •