mysql conditional queries

I have 3 tables in a mysql database names Visits, Groups, Carers. A visit can be executed by a group or a carer, so in the Vi开发者_如何学Pythonsit table I have a field carer mode, set to 1 if the visit is exectuted by a carer and set to 0 if executed by a group.

I need to build a statement that will retrieve either the group name or the carer name (depending on the value of carer_mode)

I realise the code below is incorrect but it might help to explain the logic I am trying to achieve:

SELECT CASE carer_mode
WHEN  '1' THEN (SELECT name FROM carers WHERE Carers.id = Visits.carer_id )
WHEN '0' THEN (SELECT name FROM groups WHERE Groups.id = Visits.carer_id )
END as carer_name
FROM `Visits`

I would appreciate any help on valid code that would help me achieve my objective

thanks

Kevin

Your code seems fine. Here's another way to achieve the same result:

    SELECT g.name
    FROM 
        Visits AS v
      JOIN
        Groups AS g
          ON g.id = v.carer_id
    WHERE v.carer_mode = 0
UNION ALL
    SELECT c.name
    FROM 
        Visits AS v
      JOIN
        Carers AS c
          ON c.id = v.carer_id
    WHERE v.carer_mode = 1

I think this will work:

  select 
  case carer_mode when '1' then c.name 
  else g.name end as carer_name 
  from Carer c 
  left join Visits v on v.carer_id=c.carer_id 
  left join Groups g on g.carer_id=c.carer_id