mysql - GROUP BY resulting in undesirable results -

in following query i'm pulling list of applicants on specific job. no matter group by, either gives me same user each different application data or 4 rows returned each.

i've begun relational database design i'm assuming set wrong. split applicants , applications , i'm struggling grouping data. unless need subquery, group_concat, or using group incorrectly?

select      applicants.*, applications.*, users.* applicants     inner join applications on applicants.job_id = applications.job_id     inner join users on applicants.user_id = users.user_id      applicants.job_id = 56 , applicants.process_level = 1 group applications.app_id 

table: applicants

+-----+--------+---------+--------+--------------------+---------------+ |  id | job_id | user_id | app_id |         applied_on | process_level | +-----+--------+---------+--------+--------------------+---------------+ |   1 |     56 |     125 |      5 |2015-05-24 19:28:55 |             1 | |   2 |     22 |      15 |      6 |2015-05-25 16:38:24 |             2 | |   3 |     56 |     100 |      7 |2015-05-26 13:38:24 |             1 | +-----+--------+---------+--------+--------------------+---------------+ 

table: applications

+---------+--------+---------+--------------+-------------+ |  app_id | job_id | user_id |   experience |    location | +---------+--------+---------+--------------+-------------+ |       5 |     56 |     125 | bibendum jus |     suscipi | |       6 |     22 |      15 |  morbi vitae |     aliquet | |       7 |     56 |     100 | duis et ex |   convallis | +---------+--------+---------+--------------+-------------+ 

table: users

+---------+-----------------+------------+-----------+ | user_id |      user_email | first_name | last_name | +---------+-----------------+------------+-----------+ |      15 |  joes@email.com |        joe |    shcomo | |     100 | sally@email.com |      sally |     smith | |     125 | johnj@email.com |       john |       doe | +---------+-----------------+------------+-----------+ 

desired result: e.g. want single row user 125 job_id 56 data applicant, applications , user. (not data represented of course):

+---------+------------+-----------+---------------+---------------------+ | user_id | first_name |  location | process_level |          applied_on | +---------+------------+-----------+---------------+---------------------+ |     125 |       john |   suscipi |             1 | 2015-05-24 19:28:55 | |     100 |      sally | convallis |             1 | 2015-05-26 13:38:24 | +---------+------------+-----------+---------------+---------------------+ 

first, note on normalization: shouldn't store job_id , user_id in both applicants , application table. likely, need them in 'application' table, since can go applicant => application determine information. storing relationships in 2 tables, open anomalies won't like.

that being said, don't need group anything. there consistent relationship between users , applications can using joins have already. strengthened these joins using user_id, job_id , app_id columns related between applications , applicants:

select u.user_id, u.first_name, a.location, ap.process_level, ap.applied_on users u join applications on a.user_id = u.user_id join applicants ap on ap.user_id = a.user_id , ap.app_id = a.app_id , ap.job_id = a.job_id ap.job_id = 56 , ap.process_level = 1; 

this worked great in sql fiddle.