SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Help with SELECT command - JOIN?

    Hi all,

    I have two tables, 'devices' and 'lads', that look like this:

    devices
    Code:
    mysql> select * from `devices` where `team` = 'both' or `team` = 'sml';
    +----+-----------+------+-------+
    | id | name	  | team | order |
    +----+-----------+------+-------+
    |  1 | Banana	| sml  |	 1 |
    |  2 | Mandarin  | sml  |	 2 |
    |  4 | Egg	   | sml  |	 4 |
    |  5 | Pineapple | both |	 5 |
    |  6 | Cherries  | both |	 6 |
    +----+-----------+------+-------+
    5 rows in set (0.00 sec)
    lads
    Code:
    mysql> select * from `lads` where `team` = 'sml';
    +----+-------+------+-------+-------+-------+-------+-------+-------+
    | id | name  | team | dev_1 | dev_2 | dev_3 | dev_4 | dev_5 | dev_6 |
    +----+-------+------+-------+-------+-------+-------+-------+-------+
    |  1 | JoeB  | sml  |	 0 |	 0 |	 0 |	 0 |	 0 |	 0 |
    |  2 | Rob   | sml  |	 0 |	 0 |	 0 |	 0 |	 0 |	 0 |
    |  3 | AlexJ | sml  |	 0 |	 0 |	 0 |	 0 |	 0 |	 0 |
    +----+-------+------+-------+-------+-------+-------+-------+-------+
    3 rows in set (0.00 sec)
    In `lads`, `dev_1` `dev_2` etc correspond to the `id ` of the fruit in `devices`.

    Now, what I want to do is when I query the lads table, only select the dev_# columns that match what is shown in the top query (i.e. only ones that are for the 'sml' or 'both' teams). Does that make sense? Any guidance would be much appreciated.

    Thanks all!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the query will end up look absolutely horrible

    please consider normalizing the lads table, removing the dev_1 through dev_6 coluomns, creating a new table like this --

    CREATE TABLE lads
    ( id INTEGER NOT NULL PRIMARY KEY
    , name VARCHAR(37) NOT NULL
    , team VARCHAR(9)
    );
    CREATE TABLE lad_devs
    ( id INTEGER NOT NULL PRIMARY KEY
    , dev INTEGER
    , PRIMARY KEY ( id , dev )
    );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Let me just explain what the object of this is. Basically, there are 2 teams of lads - small and enterprise. The products they can sell are stored in devices. Some devices can be sold by the small team, some can be sold by the enterprise team, and some can be sold by both. The number of products that each individual lad has sold is stored in dev_1, dev_2 etc.

    What I want to be able to do, is run a query that shows me all the lads on the small team (for example) and how many of each product they have sold. However, because the small team can't sell enterprise products, I don't need them included in the output.

    Make more sense? And no, they're not selling eggs and fruit, I've just changed the device names in the example above

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that helps, i guess

    my response is the same -- do not have multiple columns of the same thing in the same row

    do a search for first normal form
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I create a third table with the numbers of products sold though, I will still have to query for the name of the device in `devices` and the user's name in `lads`, surely that creates much more work?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, far less work, because it's only one join, not 6 CASE expressions with 6 subquery joins
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok thanks a lot


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
  •