Conditional Query Help Please

Databases
1

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 what i need help with:

I want to be able to dynamically create column “offer_211” from “offer” table

so for all the offer_id’s as (columns) would be from offer table

Output:

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

Desired Output:

| 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 | |

2

Hi and welcome to the SP forums :slight_smile:

How? I mean, what is the logic to create that column from that table? Could you explain in plain english (no sql code) what it is you are trying to achieve?

And something tells me the query you posted isn’t complete… :wink:

3

This is going to be a stored procedure guido - It needs to be in pure SQL no php

4

I think you can’t… but I might be mistaken. Let’s see what the others have to say about it :slight_smile:

5

i would definitely use the simplest query to return the data, and then, while looping over the result set in php, build the crosstab layout there

6

This query


SELECT
    userlist.USERID
  , offer.ID AS OFFERID
  , MAX(CASE 
          WHEN track.ID IS NULL THEN 0
          ELSE 1
        END) AS CLICKED
FROM userlist
CROSS JOIN offer
LEFT OUTER JOIN track
ON  track.OFFER_ID = offer.ID
AND track.USERID = userlist.USERID
GROUP BY 
    userlist.USERID
  , offer.ID
ORDER BY
    userlist.USERID
  , offer.ID

should give you a list with all offers for all users and for each user/offer combination the value 1 if that user has clicked on that offer and 0 if he hasn’t.

Now you’ll have to loop through the result set and create the output you want, using your server side language (PHP?).

7

Hi guido,

Purpose of what i am trying to figure out:

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 |

thanks!!