mysql - List users by program -

having tables these:

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 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 list users belonging particular program, this?

   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 

(number of programs may change according program table data, it's limited ~20).

you can try using following query:

select     user.user_id,     if(program.name = 'program 1', 'y', 'n') 'program 1'     if(program.name = 'program 2', 'y', 'n') 'program 2'     if(program.name = 'program 3', 'y', 'n') 'program 3'     ... 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 program columns won't win award being prettiest query, should job done.