Either I'm misunderstanding what you're trying to do or that Oracle query is
a really complicated way of doing something simple.
create table Customer ( name text, id int auto_increment primary key );
create table Skill ( description text, id int auto_increment primary key );
create table CustomerSkills ( customer int NOT NULL, skill int NOT NULL );
insert into Customer (name) values
('Steve'),('Harry'),('Rachel'),('Iggy'),('Harold'),('Saori');
select * from Customer;
+--------+----+
| name | id |
+--------+----+
| Steve | 1 |
| Harry | 2 |
| Rachel | 3 |
| Iggy | 4 |
| Harold | 5 |
| Saori | 6 |
+--------+----+
6 rows in set (0.00 sec)
insert into Skill (description) values
('Typing'),('Dancing'),('SQL'),('Modelling'),('Judo'),('Acrobatics');
select * from Skill;
+-------------+----+
| description | id |
+-------------+----+
| Typing | 1 |
| Dancing | 2 |
| SQL | 3 |
| Modelling | 4 |
| Judo | 5 |
| Acrobatics | 6 |
+-------------+----+
6 rows in set (0.00 sec)
insert into CustomerSkills (customer, skill)
select Customer.id, Skill.id from Customer,Skill
where Customer.name='Steve' and Skill.description in ('Typing','SQL');
insert into CustomerSkills (customer, skill)
select Customer.id, Skill.id from Customer,Skill
where Customer.name='Harry' and Skill.description in
('Typing','SQL','Judo');
insert into CustomerSkills (customer, skill)
select Customer.id, Skill.id from Customer,Skill
where Customer.name='Rachel' and Skill.description in ('Dancing');
insert into CustomerSkills (customer, skill)
select Customer.id, Skill.id from Customer,Skill
where Customer.name='Iggy' and Skill.description in ('Dancing','Judo');
insert into CustomerSkills (customer, skill)
select Customer.id, Skill.id from Customer,Skill
where Customer.name='Harold' and Skill.description in ('SQL','Modelling');
insert into CustomerSkills (customer, skill)
select Customer.id, Skill.id from Customer,Skill
where Customer.name='Saori' and Skill.description in
('Typing','Dancing','SQL','Modelling','Judo','Acrobatics');
select Customer.name, Skill.description from Customer,Skill,CustomerSkills
where Customer.id = CustomerSkills.customer
and Skill.id = CustomerSkills.skill;
+--------+-------------+
| name | description |
+--------+-------------+
| Steve | Typing |
| Steve | SQL |
| Harry | Typing |
| Harry | SQL |
| Harry | Judo |
| Rachel | Dancing |
| Iggy | Dancing |
| Iggy | Judo |
| Harold | SQL |
| Harold | Modelling |
| Saori | Typing |
| Saori | Dancing |
| Saori | SQL |
| Saori | Modelling |
| Saori | Judo |
| Saori | Acrobatics |
+--------+-------------+
16 rows in set (0.00 sec)
select Customer.name, Skill.description from Customer,Skill,CustomerSkills
where Customer.id = CustomerSkills.customer
and Skill.id = CustomerSkills.skill
and Skill.description in ('SQL');
+--------+-------------+
| name | description |
+--------+-------------+
| Steve | SQL |
| Harry | SQL |
| Harold | SQL |
| Saori | SQL |
+--------+-------------+
4 rows in set (0.01 sec)
select Customer.name, Skill.description from Customer,Skill,CustomerSkills
where Customer.id = CustomerSkills.customer
and Skill.id = CustomerSkills.skill
and Skill.description in ('SQL','Dancing');
+--------+-------------+
| name | description |
+--------+-------------+
| Steve | SQL |
| Harry | SQL |
| Rachel | Dancing |
| Iggy | Dancing |
| Harold | SQL |
| Saori | Dancing |
| Saori | SQL |
+--------+-------------+
7 rows in set (0.01 sec)
Of course, you'll probably want to use Skill.id instead of skill.description
for your WHERE...
Were you trying to do something more complex than this?
-rob
On 7/6/02 at 2:52 pm, Hekuran Vokshi <[EMAIL PROTECTED]> wrote:
> Hi everybody,
>
> I'm new to MySQL and have got the following code that works with Oracle
> SQL... After looking at the documentation I realised that DECODE seems to
be
> reserved for encryption... any idea of how this would work with MySQL?
Here
> is the code:
>
> The tables are as follows
> table table
> table
> CUSTOMER CUSTOMER_SKILLS SKILL
> CUSTOMER_ID (p) CUSTOMER_ID(f) SKILL_ID(p)
> CUSTOMER_NAME SKILL_ID (f) SKILL_DESC
>
> (p) Primary Key
> (f) Foreign Key
>
> The idea is to select the customer(s) that have 'n' skill(s) and return
the
> customer name and skill(s) description. The skills are know from the user
> input.
>
> SELECT
> DECODE (A.SKILLS_ID, B.SKILLS_ID, D.CUSTOMER_NAME, NULL) "Customer
> Name", A.SKILL_DESC "Skill Title"
> FROM CUSTOMER_DETAILS D, CUSTOMER_SKILLS C, SKILLS B, SKILLS A
>
> WHERE C.SKILLS_ID = A.SKILLS_ID
> AND D.CUSTOMER_ID = C.CUSTOMER_ID
> AND A.SKILLS_ID IN (1,3,5)
>
> AND B.SKILLS_ID = (SELECT MIN(A.SKILLS_ID)
> FROM CUSTOMER_SKILLS A
> WHERE A.CUSTOMER_ID =
> D.CUSTOMER_ID
> AND A.SKILLS_ID IN (1,3,5) )
> ORDER BY D.CUSTOMER_NAME, 2;
>
> > Thanks in advance
> > Heki
> >
> >
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
> >
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
