LEFT JOIN to a single row in order of criteria in MySQL

Question

Ok, I tried to simplify my question by abstracting away the details but I'm afraid I wasn't clear and didn't meet moderator requirements. So I will post the full query with my problem in more detail and the actual query I am struggling with. If the question is still inadequate, could you please comment with specifics about what is unclear and I will do my best to clarify.

First, here is the current query that returns all assignment rows for each bed:

SELECT 
  beds.bed_id,
  beds.bedstatus,
  beds.position as bed_position,
  rooms.room_id,
  rooms.room,
  wings.wing_id,
  wings.name as wing_name,
  buildings.building_id,
  buildings.name as building_name,
  assignments.assignment_id,
  assignments.student_id,
  assignments.assign_dt,
  assignments.assigned_by,
  assignments.assignment_status,
  assignments.expected_arrival_dt as arrival_dt,
  assignments.room_charge_type,
  students.first_name,
  students.last_name,
  meal_plans.name as meal_plan_name,
  room_rates.rate_name
FROM
  beds
LEFT JOIN 
  rooms ON (beds.room_id = rooms.room_id)
LEFT JOIN
  wings ON (rooms.wing_id = wings.wing_id)
LEFT JOIN
  buildings ON (wings.building_id = buildings.buildings_id)
LEFT JOIN assignments ON 
  ((beds.bed_id=assignments.bed_id) AND (term_id = @term_id))
LEFT JOIN
  students ON (assignments.student_id = students.student_id)
LEFT JOIN
  meal_plans ON (assignments.meal_plan_id = meal_plans.meal_plan_id)
LEFT JOIN
  room_rates ON (room_rate_id = room_rates.room_rate_id)
WHERE
  (
    (rooms.room IS NOT NULL) AND
    (rooms.assignable = 1) AND
    (buildings.active = 1) AND
    (buildings.building_id = @building_id)
  )
ORDER BY BY rooms.room;

The problem is that there may be multiple rows in the "assignments" table for each room distinguished by the "assignment_status" field and I want a single row for each assignment. I want to determine which assignment row to select based on the value in assignment_status. That is if the assignment status is "active", I want that row, otherwise, if there is a row with status "waiting approval" then I want that row, etc...

Barmar's suggestion is given here:

LEFT JOIN (SELECT *
       FROM OtherTable
       WHERE <criteria>
       ORDER BY CASE status
                    WHEN 'Active' THEN 1
                    WHEN 'Waiting Approval' THEN 2
                    WHEN 'Canceled' THEN 3
                    ...
                END
       LIMIT 1) other

This was very helpful and I attempted this approach:

SELECT 
  beds.bed_id,
  beds.bedstatus,
  beds.position as bed_position,
  rooms.room_id,
  rooms.room,
  wings.wing_id,
  wings.name as wing_name,
  buildings.building_id,
  buildings.name as building_name,
  assign.assignment_id,
  assign.student_id,
  assign.assign_dt,
  assign.assigned_by,
  assign.assignment_status,
  assign.expected_arrival_dt as arrival_dt,
  assign.room_charge_type,
  students.first_name,
  students.last_name,
  meal_plans.name as meal_plan_name,
  room_rates.rate_name
FROM
  beds
LEFT JOIN 
  rooms ON (beds.room_id = rooms.room_id)
LEFT JOIN
  wings ON (rooms.wing_id = wings.wing_id)
LEFT JOIN
  buildings ON (wings.building_id = buildings.buildings_id)

LEFT JOIN (SELECT *
           FROM assignments
           WHERE ((assignments.bed_id==beds.bed_id) AND (term_id = @term_id))
           ORDER BY CASE assignment_status
                        WHEN 'Active' THEN 1
                        WHEN 'Waiting Approval' THEN 2
                        WHEN 'Canceled' THEN 3
                    END
           LIMIT 1) assign  

LEFT JOIN
  students ON (assign.student_id = students.student_id)
LEFT JOIN
  meal_plans ON (assign.meal_plan_id = meal_plans.meal_plan_id)
LEFT JOIN
  room_rates ON (room_rate_id = room_rates.room_rate_id)
WHERE
  (
    (rooms.room IS NOT NULL) AND
    (rooms.assignable = 1) AND
    (buildings.active = 1) AND
    (buildings.building_id = @building_id)
  )
ORDER BY rooms.room;

But I realized, the problem here is that OtherTable (assignments) is joined to the parent query based on a FK:

  ((beds.bed_id=assignments.bed_id) AND (term_id = @term_id))

So I can't do the subselect as the beds.bed_id isn't in scope for the subselect. So as Barmar's comment indicates the join criteria needs to be outside the subselect--but I'm having trouble figuring out how to both restrict the results to a single row per room and move the join outside the subselect. I'm wondering if travelboy's suggestion to use GROUP BY may be more fruitful, but haven't been able to determine how the grouping should be done.

Let me know if I can provide additional clarification.

Original Question:

I need from Table A to do a LEFT JOIN on a SINGLE row in another table, Table B meeting certain criteria (there may be multiple or no rows in Table B that meet the criteria). If there are multiple rows I want to select which row in B to join based on the value of a field in Table B. For example, if there is a row in B with status column='Active', I want that row, if not, if there is a row with status='Waiting Approval', I want that row, if there is a row with status='Canceled', I want that row, etc... Can I do this without a sub select? With a sub select?

Answer 1

I need to follow up with some additional testing to make sure this is accomplishing my goal--but I think I've done this using travelboy's suggestion of a group by query combined with barmar's case logic (wish I could split the answer). Here's the query:

SELECT 
  beds.bed_id,
  beds.bedstatus,
  beds.position as bed_position,
  rooms.room_id,
  rooms.room,
  wings.wing_id,
  wings.name as wing_name,
  buildings.building_id,
  buildings.name as building_name,
  assignments.assignment_id,
  assignments.student_id,
  assignments.assign_dt,
  assignments.assigned_by,
  assignments.assignment_status,
  assignments.expected_arrival_dt as arrival_dt,
  assignments.room_charge_type,
  MIN(CASE assignments.assignment_status
         WHEN 'Active' THEN 1
         WHEN 'Waiting Approval' THEN 2
         WHEN 'Canceled' THEN 3
       END),
  students.first_name,
  students.last_name,
  meal_plans.name as meal_plan_name,
  room_rates.rate_name
FROM
  beds
LEFT JOIN 
  rooms ON (beds.room_id = rooms.room_id)
LEFT JOIN
  wings ON (rooms.wing_id = wings.wing_id)
LEFT JOIN
  buildings ON (wings.building_id = buildings.building_id)

LEFT JOIN assignments
  ON ((assignments.bed_id=beds.bed_id) AND (term_id = 28))
LEFT JOIN
  students ON (assignments.student_id = students.student_id)
LEFT JOIN
  meal_plans ON (assignments.meal_plan_id = meal_plans.meal_plan_id)
LEFT JOIN
  room_rates ON (assignments.room_rate_id = room_rates.room_rate_id)
WHERE
  (
    (rooms.room IS NOT NULL) AND
    (rooms.assignable = 1) AND
    (buildings.active = 1)
  )
GROUP BY
  bed_id
ORDER BY rooms.room;

Answer 2

In some cases (but not in all cases) you can do it without a sub-select. You would need to GROUP BY a unique field in table A, typically an ID. This ensures that you get only one (or none) row from table B. However, selecting the row you want is the tricky part. You need an aggregating function such as MAX(). If the field in B is a number, that's easy to do. If not, you can apply some SQL functions on the fields in B to calculate something like a score to sort by. For example, Active could correspond to a higher value than Cancelled etc. That will work without a sub-select and likely be faster on big data sets.

With a sub-select it's easy to do. You can either use Barmar's solution, or, if you only need one specific field from B, you can also put the sub-select within the SELECT clause of the outer query.

Answer 3

Use:

LEFT JOIN (SELECT *
           FROM OtherTable
           WHERE <criteria>
           ORDER BY CASE status
                        WHEN 'Active' THEN 1
                        WHEN 'Waiting Approval' THEN 2
                        WHEN 'Canceled' THEN 3
                        ...
                    END
           LIMIT 1) other