List of users by program
The presence of such tables:
Users
CREATE TABLE `affiliate__model__user_node` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
Programs:
CREATE TABLE `affiliate__model__program` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB
Users using the programs:
CREATE TABLE `affiliate__model__user_program` (
`user_id` bigint(20) NOT NULL DEFAULT '0',
`program_id` bigint(20) NOT NULL DEFAULT '0',
`username` varchar(255) NOT NULL,
PRIMARY KEY (`user_id`,`program_id`)
)
How to efficiently list users belonging to a specific program, for example?
user_id | program 1 | program 2 | program 3 | program N ....
--------------+-----------+-----------+-----------+-----------
1 | Y | N | N | Y
3 | N | N | N | N
7 | N | Y | N | N
12 | Y | Y | Y | Y
n | N | N | N | Y
(the number of programs can vary according to the tabular data program
, but it is limited to ~ 20).
+3
source to share
1 answer
You can try using the following query:
SELECT
user.user_id,
IF(program.name = 'program 1', 'Y', 'N') AS 'program 1'
IF(program.name = 'program 2', 'Y', 'N') AS 'program 2'
IF(program.name = 'program 3', 'Y', 'N') AS 'program 3'
...
FROM affiliate__model__user_node user
INNER JOIN affiliate__model__user_program model
ON user.id = model.user_id
INNER JOIN affiliate__model__program program
ON model.program_id = program.id
For ~ 20 programmable columns, this won't win the prettiest query award, but it should get the job done.
0
source to share