Adding a results column from a query

Question

I have a users table, a positions table, and an associate users_positions table.

It looks something (minimally) like:

USERS
-----
id

USERS_POSITIONS
---------------
id
user_id (FK)
position_id (FK)
calendar_date (a specific date)

POSITIONS
---------
id
day (a day of the week)
title

Users can volunteer for zero or more positions on a position's day, which goes into the users_positions table along with a calendar_date. I'm populating a form with the positions, along with the record of whether the user has or has not volunteered for that position (which will be a checkbox), because the user is able to edit their record as any time.

To start off with, I've managed to pull the positions from the database, and also managed to pull a join which only returns the the positions the user has volunteered for, but was wondering if MySQL has a feature that would create some kind of extra column from the query that would return all the positions and in the extra column, mark them as volunteered for or not. With this, I could check or uncheck those checkboxes with the information when generating the form.

The only other way I could think of would be to use php to get an array of the position rows for that day, then an array of the join rows which showed which rows the user had volunteered for. Then iterate through them both creating a new array with the data I described in the previous paragraph.

If there was a way to do this in MySQL though, it would of course be preferred. So can MySQL do something like this?

Barmar · Accepted Answer · 2016-04-08 15:51:19Z

2

Use LEFT JOIN. This will return all the rows from the first table, even if they don't match the table you're joining with. If there's no match, the values in the second table will be NULL, and you can test for that when showing whether the position has a volunteer.

SELECT day, title, IF(up.id IS NULL, "No", "Yes") volunteered
FROM Positions AS p
LEFT JOIN Users_Positions AS up ON p.id = up.position_id AND up.user_id = @user_id

edited Apr 8, 2016 at 15:51

answered Apr 7, 2016 at 22:07

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

xdola Over a year ago

Can you explain me what "up" mean?

Barmar Over a year ago

It's a table alias, so you don't have to write out the full table name every time you refer to a column.

Barmar Over a year ago

Users_Positions AS up makes up an alias for Users_Positions.

Barmar Over a year ago

This is basic SQL syntax.

xdola Over a year ago

Ah yes sorry, I was missing the third line where I can see "AS" keyword. Thx

|

Collectives™ on Stack Overflow

Adding a results column from a query

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related