lundi 22 août 2016

MYSQL (WHERE - IF - SELECT/IN statement)

I'm trying to set my code up where I have a Control Panel for my Sales Reps. From my Control Panel, I am able to control which customers a Rep calls based on the item the customer bought.

i.e. If I assign "Cars" in my Control Panel for Sales Rep 01, he would only call customers that bought toys in the "Cars" category. And all different toys in my "Cars" category are stored in a separate file named "Vehicles".

My files are as follows:

customer (File)
+--------+-----------+--------+
|  name  | phone     | toy    |
+--------+-----------+--------+
|  Gail  | 777-1234  | Truck  |
|  June  | 777-1235  | Doll   |
|  Mary  | 777-1236  | Racer  |
|  Bill  | 777-1237  | Ball   |
|  Jon   | 777-1238  | Jeep   |
+--------+-----------+--------+



control_panel (File)
+----------+--------+
| user     | desc   | 
+----------+--------+
| sales_01 | Cars   |
+----------+--------|


vehicles (File)
+---------+
+  item   | 
+---------+
|  Truck  |
|  Racer  |
|  Jeep   |
+---------+

In trying to test this code out, I have this portion of my code working.

select 
    c.name , c.phone
FROM 
    customer c
WHERE
    c.toy IN (
            SELECT 
                v.item 
            FROM 
                vehicles v
            )

Now I'm trying to condition my WHERE statement so that only if I choose "Cars" in my Control Panel screen for User "sales_01", then customers who bought Cars will only show on the Call Screen for User "sales_01".

This is one example of some of the code I've been testing but cant get to work correctly.

SELECT   
    c.name , c.phone
FROM
    customer c , control_panel p
WHERE
    (IF p.desc = "Cars" 
        THEN (c.toy 
            IN (SELECT 
                    v.item 
                FROM
                    vehicles v)
             )
     END)

Any help is appreciated. Thx.

Aucun commentaire:

Enregistrer un commentaire