Help with SELECT command - JOIN?

[dannybeckett]

Hi all,

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

devices

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

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!

[r937]

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 )
);

[dannybeckett]

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

[r937]

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

[dannybeckett]

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?

[r937]

no, far less work, because it's only one join, not 6 CASE expressions with 6 subquery joins

[dannybeckett]

Ok thanks a lot