Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2004
    Posts
    2

    Unanswered: mysql IF or CASE help

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •