select from combined columns

Discussion in 'Databases' started by catapop, Oct 27, 2008.

0
  1. #1
    hi. I have the following table

    +------+---------------+---------------+
    | id | name | description |
    +------+---------------+---------------+
    | 1 | drive_type | DVD-RW |
    | 1 | hdd_capacity | 500GB |
    | 2 | drive_type | DVD-RW |
    | 2 | hdd_capacity | 500GB |
    | 3 | drive_type | COMBO |
    | 4 | hdd_capacity | 500GB |
    +------+---------------+---------------+

    I want to make a select where same id has drive_type="DVD-RW" and hdd_capacity="500GB"

    The selected table should look like this

    +------+---------------+---------------+
    | id | name | description |
    +------+---------------+---------------+
    | 1 | drive_type | DVD-RW |
    | 1 | hdd_capacity | 500GB |
    | 2 | drive_type | DVD-RW |
    | 2 | hdd_capacity | 500GB |
    +------+---------------+---------------+

    so product with the id=1 and id=2 has drive_type="DVD_RW" and
    hdd_capacity="500GB"


    can you help me with a query? can this be done in a single query?
    Thanks
     
    catapop, Oct 27, 2008 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    This is a very inefficient database structure but, I think this should work...

    SELECT id, name, description FROM my_table
    WHERE id IN
    (SELECT id FROM my_table WHERE name = 'drive_type' AND description = 'DVD-RW')
    AND id IN
    (SELECT id FROM my_table WHERE name = 'hdd_capacity' AND description = '500GB ')
     
    jestep, Oct 27, 2008 IP
  3. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #3
    Actually. I think this would be better at preventing dups...

    SELECT id, name, description FROM my_table
    WHERE id IN
    (SELECT id FROM my_table WHERE name = 'drive_type' AND description = 'DVD-RW'
    AND id IN
    (SELECT id FROM my_table WHERE name = 'hdd_capacity' AND description = '500GB '))
     
    jestep, Oct 27, 2008 IP
  4. catapop

    catapop Peon

    Messages:
    79
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #4
    ok. thanks. I'll try it.
     
    catapop, Oct 27, 2008 IP
  5. chisara

    chisara Peon

    Messages:
    141
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #5
    If you don't need the duplicate records, then try this
    SELECT id FROM mytable WHERE (name="drive_type" AND description="DVD-RW") OR ( name="hdd_capacity" AND description="500GB") GROUP BY id HAVING COUNT(id)=2;

    Looking at your query you don't really need the name and description columns in your resultset, since that is the information from your where clause. The group by finds the id's that contain the queried number of names(properties) in this case two.

    I concur with JESTEP with regards to the "strange" schema.
     
    chisara, Oct 28, 2008 IP