mysql IF or CASE help

chucklarge · #1 (**permalink**) 08-21-06, 15:38

Hello

I am trying to figure out if i can use the mysql control structures in this way.

-Primary-
+-----+----+
| id | name |
+-----+----+
| 1 | Dave |
| 2 | Jason |
| 3 | Jeff |
| 6 | Jeremy |
| 9 | Sam |
+-----+----+

-Shipping-
+-----+----+
| id | name |
+-----+----+
| 1 | Martin|
| 2 | Andrew |
| 3 | Dana |
| 4 | Chris |
| 5 | John |
+-----+----+

-Relation-
+-----+----+------+
| id | type | ps_id |
+-----+----+------+
| 1 | Shipping| 3 |
| 2 | Shipping| 5 |
| 3 | Primary | 9 |
| 4 | Shipping| 1 |
| 5 | Primary | 1 |
+-----+----+------+

Bascially I have two tables with the same fields. I have a 3rd table that that tells me the table and id to select. Right Now, I am doing this with php, but need to find a way to do this with a mysql statement. Can anyone help me with the the control flow statement that will produce the following output ?

Dana
John
Sam
Martin
Dave

Thanks,
Chuck

r937 · #2 (**permalink**) 08-21-06, 17:51

Code:

select coalesce(p.name,s.name) as name
  from Relation as r
left outer
  join `Primary` as p
    on p.id = r.ps_id
   and        r.type = 'Primary'
left outer
  join `Shipping` as s
    on s.id = r.ps_id
   and        r.type = 'Shipping'

Internet Services

Web Development

Digital Marketing

Consumer Tech