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 thi开发者_开发知识库s 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.

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.

What your trying to do sounds like a pivot.

Most solutions seem to revolve around one of the following approaches:

• Creating a dynamic query, as in Is there a way to pivot rows to columns in MySQL without using CASE?
• Selecting all the attribute columns, as in How to pivot a MySQL entity-attribute-value schema
• Or, identifying the columns and using either a CASE statement or a user defined function as in pivot in mysql queries

I don't think this is possible. Actually I think this is just a matter of data representatioin. I would try to use a component to display the data that would allow me to pivot the data (for instance, the same way you do on excel, open office's calc, etc).

To take it one step further, you should think again why you need this and probably try to solve it in the application not in the database.

I know this doesn't help much but it's the best I can think of :(