SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2005
    Posts
    436
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Confusing Database Work

    OK so first off, this is essentially what I would like my php script to echo.

    Group 1:
    Part 1-1
    Part 1-2
    Part 1-3
    Part 1-4

    Group 2:
    Part 2-1
    Part 2-2
    Part 2-3
    Part 2-4

    These are my tables:

    Parts:
    Code:
    +----------+----------+----------+
    | Part     | Group    | Default  |
    +----------+----------+----------+
    +----------+----------+----------+
    | Part 1-1 | 1        | 1        |
    +----------+----------+----------+
    | Part 1-2 | 1        | 0        |
    +----------+----------+----------+
    | Part 1-3 | 1        | 0        |
    +----------+----------+----------+
    | Part 1-4 | 1        | 0        |
    +----------+----------+----------+
    | Part 2-1 | 2        | 1        |
    +----------+----------+----------+
    | Part 2-2 | 2        | 0        |
    +----------+----------+----------+
    | Part 2-3 | 2        | 0        |
    +----------+----------+----------+
    | Part 2-4 | 2        | 0        |
    +----------+----------+----------+
    Combos:
    Code:
    +----------+----------+
    | Part     | Group    |
    +----------+----------+
    +----------+----------+
    | Part 2-3 | 2        |
    +----------+----------+
    When I run my code, I want it to echo each groups part, and the part set for that group. If I was looking for the part set for group 1, it is the default for that group because nothing is set in the combos database. For part 2, however, Part 2-3 is set in the combos database so that should be the default.

    I need a script to echo every part, and make the selelcted part bold. You dont need to actually right the script, just a flow would help. I cannot think of a way to work around having 2 bold options, the default and the set combo in the database.

    Currently I have it set up to make the selected part bold, and do not have default parts added in.
    First, I read every part from the database where the group number is predefined. Then for each part, I do another query to see if that is the set part. I am thinking this is a horrible way to do it, though.

    Any help is appreciated.

    Thanks,
    e39m5

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    a LEFT OUTER JOIN query would allow you to use a single query instead of two of them

    i wouldn't call two queries "horrible" (because each will be using a very specific WHERE clause, right?), but a single query is definitely better
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Jun 2005
    Posts
    436
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I know I said I wouldn't need the actual code, but Ive never used joins before, and I cant seem to figure it out. What would my query need to be?

    Thanks,
    e39m5

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    actually now that i think about it, the LEFT JOIN is clumsier

    try this instead --
    Code:
    select P.Part
         , P.Group
         , case when exists
                 ( select 1
                     from Combos
                    where Group = P.Group
                      and Part = P.Part )
                then 'yes'
                when P.Default = 1
                then 'yes'
                else 'no' end   as this_part_is_bold    
      from Parts as P
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •