MySQL Dynamic Query Challenge - Help please!!

Question

Hello,

I have this query:

---

   SELECT  
    userlist.USERID,  
    (case when (sum( CASE WHEN track.OFFER_ID = 221  THEN 1 ELSE 0 END) > 1) then 1 else 0 end) offer_211

FROM  
userlist  
INNER JOIN track ON userlist.USERID = track.USERID  

group by
userid

---

This is the output:

+------------+----------
| USERID     | offer_211 |
+------------+----------
| 1657487706 |         0 |  
| 1238439394 |         0 |  
| 1238427171 |         1 |  
| 1248431441 |         0 |  
| 1248464345 |         1 |  

Purpose of this query:

There is a user table, offer table and track table. Track table contains all the clicks from user table. I want to create a view like above, which will show me all the clicks from users for all the offers. If the user clicked on a certain offer it will display '1' under that offer column, if not it will display '0'. All the records are coming from track table.

Here is what i need help with: I want to be able to dynamically create columns "offer_211" from "offer" table instead of specifing them manually.(see my query)

So, for all the offers that a user clicked, or not clicked (columns) would be from offer the table.

it should be like this: "offer_"+offer.ID

It should look like this: (assuming there are only 2 offers in the offer table with ids 211, 212)

There could be 100's of offers in the offer table so there should be the same amount of dynamic columns for this view.

| USERID     | offer_211 | offer_212 |
+------------+----------+-------------
| 1657487706 |         0 |         1 |
| 1238439394 |         0 |         0 |
| 1238427171 |         1 |         0 |
| 1248431441 |         0 |         1 |
| 1248464345 |         1 |         0 |

track Table:

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| ID         | int(22)     | NO   | PRI | NULL    | auto_increment |
| OFFER_ID   | int(22)     | YES  | MUL | NULL    |                |
| USERID     | int(22)     | YES  | MUL | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

userlist Table:

+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| USERID      | int(22)      | NO   | PRI | 0       |       |
| EMAIL       | varchar(200) | YES  |     | NULL    |       |
| FIRSTNAME   | varchar(100) | YES  |     | NULL    |       |
| LASTNAME    | varchar(100) | YES  |     | NULL    |       |

offer Table:

+------------------+--------------+------+-----+-------------+----------------+
| Field            | Type         | Null | Key | Default     | Extra          |
+------------------+--------------+------+-----+-------------+----------------+
| ID               | int(11)      | NO   | PRI | NULL        | auto_increment |
| NAME             | varchar(100) | YES  |     | NULL        |                |
| DESCRIPTION      | text         | YES  |     | NULL        |                |
| URL              | text         | YES  |     | NULL        |                |

Answer 1

Have you considered getting a list of user IDs and offer IDs, sorted first by user ID and next by offer ID? It's the same information, just organized slightly differently, plus it'd be much, much faster for the database to execute a query like "select user_id, offer_id from track".

Answer 2

Mike,

Here what it returns: See at the end how its cut off - weird. What could it be?

I also didn't understand the last part of your comment - Last block;

Create and execute a prepared statement from the dynamic SQL:

PREPARE stmt FROM @sql;
EXECUTE stmt;

+---------+---------+---------+---------+
| offer_1 | offer_2 | offer_3 | offer_4 |
+---------+---------+---------+---------+
|       3 |       2 |       1 |       1 |
+---------+---------+---------+---------+

DEALLOCATE PREPARE stmt;

Here is the new query:

SELECT CONCAT(
    'SELECT',
    GROUP_CONCAT(
        ' SUM(IF(OFFER_ID = ', offer_id,
        ', 1, 0)) AS offer_', offer_id),
    ' FROM track;')
INTO @sql
FROM (SELECT DISTINCT ID as OFFER_ID from offer where `STATUS`=1) AS track;

select @sql;

Here is the output:

SELECT SUM(IF(OFFER_ID = 178, 1, 0)) AS offer_178, SUM(IF(OFFER_ID = 234, 1, 0)) AS offer_234, SUM(IF(OFFER_ID = 206, 1, 0)) AS offer_206, SUM(IF(OFFER_ID = 213, 1, 0)) AS offer_213, SUM(IF(OFFER_ID = 229, 1, 0)) AS offer_229, SUM(IF(OFFER_ID = 220, 1, 0)) AS offer_220, SUM(IF(OFFER_ID = 221, 1, 0)) AS offer_221, SUM(IF(OFFER_ID = 222, 1, 0)) AS offer_222, SUM(IF(OFFER_ID = 225, 1, 0)) AS offer_225, SUM(IF(OFFER_ID = 226, 1, 0)) AS offer_226, SUM(IF(OFFER_ID = 257, 1, 0)) AS offer_257, SUM(IF(OFFER_ID = 259, 1, 0)) AS offer_259, SUM(IF(OFFER_ID = 258, 1, 0)) AS offer_258, SUM(IF(OFFER_ID = 260, 1, 0)) AS offer_260, SUM(IF(OFFER_ID = 228, 1, 0)) AS offer_228, SUM(IF(OFFER_ID = 230, 1, 0)) AS offer_230, SUM(IF(OFFER_ID = 232, 1, 0)) AS offer_232, SUM(IF(OFFER_ID = 233, 1, 0)) AS offer_233, SUM(IF(OFFER_ID = 239, 1, 0)) AS offer_239, SUM(IF(OFFER_ID = 240, 1, 0)) AS offer_240, SUM(IF(OFFER_ID = 241, 1, 0)) AS offer_241, SUM(IF(OFFER_ID = 242, 1, 0)) AS offer_242, SUM(IF(OFFER_ID = 243, 1, 0)) AS offer_243, SUM(IF(OFFE FROM track;