SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    overlapping col values and SELECT

    I have companies in a "Clothing" table in column "Supply" that contains supplier lookup values for 'hats' or 'gloves' or 'both hats & gloves' with these values:

    hats = 1
    hats & gloves = 2
    gloves = 3


    Is the best way to account for requests on either of these, hats or gloves, that will bring up those who also supply both, without using OR in the SELECT:

    <=2 (for hats)

    =>2 (for gloves)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that'll work, if you never have any other values (and if you code >=, not =>)

    how many "clothing" databases do you know that have only hats and gloves, though?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    point well taken.

    these are main groupings, like "residential" and "commercial" but "industrial could be added in future, so maybe I should start with a higher number for a little room to develop:

    hats = 4
    hats & gloves = 5
    gloves = 6

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    perhaps you'd better take the time now to design it properly

    if you need to mix hats and gloves with residential and commercial, who knows what else might pop up
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no that was a simple example to illustrate (though of course there are commercially rated gloves and hard hats, I guess residential could be used for the consumer lines)

    what I have will work

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by datadriven
    what I have will work
    yes, but only as long as there are only two values

    the day you want to add a third, you're screwed

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How would you add a third?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    with a many-to-many relationship table

    one company can supply multiple items

    one item can be supplied by multiple companies

    the relationship table would look like this --

    create table companysuppliesitems
    ( company_id integer not null
    , item_id integer not null
    , primary key ( company_id , item_id )
    , foreign key ( company_id ) references companies ( id )
    , foreign key ( item_id ) references items ( id )
    );

    so if a given company supplies three items, there'd be three rows in this table
    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
  •