How do I make the rows of a lookup table into the columns of a query?

I have three tables: students, interests, and interest_lookup.

Students has the cols student_id and name.

Interests has the cols interest_id and interest_name.

Interest_lookup has the cols student_id and interest_id.

To find out what interests a student has I do

select interests.interest_name from `students`
  inner join `interest_lookup`
    on interest_lookup.student_id = students.student_id
  inner join `interests`
    on interests.interest_id = interest_lookup.interest_id

What I want to do is get a result set like

student_id | students.name | interest_a | interest_b | ...

where the column name ‘interest_a’ is a value in interests.name and the interest_ columns are 0 or 1 such that the value is 1 when there is a record in interest_lookup for the given student_id and interest_id and 0 when there is not.

Each entry in the interests table must appear as a column name.

I can do this with subselects (which is super slow) or by making a bunch of joins, but both of these really require that I first select all the records from interests and write out a dynamic query.

Answer

You’re doing an operation called a pivot. @Slider345 linked to (prior to editing his answer) another SO post about doing it in Microsoft SQL Server. Microsoft has its own special syntax to do this, but MySQL does not.

You can do something like this:

SELECT s.student_id, s.name,
  SUM(i.name = 'a') AS interest_a,
  SUM(i.name = 'b') AS interest_b,
  SUM(i.name = 'c') AS interest_c
FROM students s
INNER JOIN interest_lookup l USING (student_id)
INNER JOIN interests i USING (interest_id)
GROUP BY s.student_id;

What you cannot do, in MySQL or Microsoft or anything else, is automatically populate columns so that the presence of data expands the number of columns.

Columns of an SQL query must be fixed and hard-coded at the time you prepare the query.

If you don’t know the list of interests at the time you code the query, or you need it to adapt to changing lists of interest, you’ll have to fetch the interests as rows and post-process these rows in your application.