#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    3
    Rep Power
    0

    Select multple records


    PLEASE HELP!
    I have 2 tables, DIVISION(DID, Name, Mgr_EID, WID) and WAREHOUSE(WID, Street_No, City, State, ZIP).

    I need to select WID, Street_No, City and State for all warehouses that have both "Computers" and "Electronics" divisions. (Computers and Electronics are in Name column in DIVISION table.

    I think I need to use an EXISTS statement, but I'm not sure - any help would be very much appreciated! THANK YOU!!!
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    21
    Rep Power
    0
    select WID, Street_No, City, State
    from WAREHOUSE
    where wid is in (select wid from DIVISION where name = 'computers' or name = 'electronics')
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    3
    Rep Power
    0
    Thanks, but that's not quite what I'm looking for - that selects any warehouse that has and Electronics OR Computers division, I need one that selects any warehouse that has BOTH Computers AND Electronics division.

    I tried switching the OR in your query to AND, but that doesn't work - doesn't return any results.

    Any other suggestions/ideas? Divisions table is below so you can see what I'm working with...

    THANK YOU!!

    Division Table:
    DID Name Mgr_EID WID
    10 Apparel 1 1
    11 Toys 6 1
    12 Sports goods 11 1
    13 Computers 16 1
    14 Utility 18 1
    21 Apparel 20 2
    22 Toys 24 2
    23 Sports goods 28 2
    24 Computers 32 2
    31 Apparel 40 3
    32 Toys 44 3
    33 Sports goods 48 3
    34 Computers 52 3
    35 Electronics 58 3
    41 Apparel 60 4
    42 Toys 64 4
    43 Sports Goods 68 4
    44 Computers 72 4
    45 Electronics 76 4
    46 Hardware 83 4
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    21
    Rep Power
    0
    okay, try:

    select WID, Street_No, City, State
    from WAREHOUSE
    where wid is in (select wid from DIVISION where name = 'electronics')
    and wid is in (select wid from DIVISION where name = 'computers')
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Cincinnati, OH USA
    Posts
    111
    Rep Power
    11
    Then why not use this:

    SELECT WID, Street_No, City, State
    FROM WAREHOUSE
    WHERE wid IN (
    SELECT wid FROM DIVISION WHERE name = 'computers')
    AND wid IN (
    or name = 'electronics')
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Cincinnati, OH USA
    Posts
    111
    Rep Power
    11
    Crap....sorry for the incomplete double-postings.. user error
    I was trying to type this, I think:


    SELECT WID, Street_No, City, State
    FROM WAREHOUSE
    WHERE wid IN (
    SELECT wid FROM DIVISION WHERE name = 'computers')
    AND wid IN (
    SELECT wid FROM DIVISION WHERE name = 'electronics')
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    3
    Rep Power
    0
    YOU ARE MY HERO.

    thank you!

IMN logo majestic logo threadwatch logo seochat tools logo