SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Quake 1 Addict CreedFeed's Avatar
    Join Date
    Feb 2002
    Location
    Milwaukee, WI
    Posts
    296
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting a "widget" and all associated attributes?

    Let's say I have the following three tables:

    WIDGETS

    id name
    1 ABC Widget
    2 XYZ Widget

    ATTRIBUTES

    id name
    1 Color
    2 Size
    3 Weight

    WIDGET_ATTRIBUTES

    widget_id attribute_id value
    1 1 Red
    1 2 Small
    2 1 Blue
    2 2 Large
    2 3 5 Pounds

    Is there an easy way to select all widgets and their associated attributes, one widget per returned row? Example results:

    widget_id widget_name Color Size Weight
    1 ABC Widget Red Small NULL
    2 XYZ Widget Blue Large 5 Pounds

    You'll note that widget #1 does not have a weight specified, so NULL would be returned (or blank, whatever).

    Perhaps it's because today is Friday, but I can't think of how to do this?!?
    -- Steve Caponetto
    Quake 1 Resurrection :: CreedFeed

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by CreedFeed View Post
    Is there an easy way to select all widgets and their associated attributes, one widget per returned row?
    not one row per widget, no, it's cumbersome and ugly

    much better to do this in your programming language, which has more flexibility for this type of cosmetic re-arranging
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Quake 1 Addict CreedFeed's Avatar
    Join Date
    Feb 2002
    Location
    Milwaukee, WI
    Posts
    296
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My ultimate end result of asking this question is I'm looking to allow for filtering of widget attributes using Sphinx Search. Unfortunately, unless I'm mistaken, Sphinx needs the data to appear within the query used to pull results in order to filter? Hrmm...
    -- Steve Caponetto
    Quake 1 Resurrection :: CreedFeed


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
  •