mysql - Joining 3+ tables using SQL displaying too many results -

im trying create query join these tables using method below. repeating results , not linking correctly. in same rating comment on 4/5 different results etc. producing 18ish results when im expecting 3. willing me problem?

select a.company_name,         f.job_id,         f.job_name,         b.user_name,         c.comments,         c.reliability,         c.rating company a,       users b,       ratings c,       usercompjobrating d,      company_job e,       jobs f d.comp_job_id = e.comp_job_id      , b.users_id = d.users_id      , c.rating_id = d.rating_id; 

many thanks,

andrew

ok tried , saying e.users_id unknown column in 'on clause'

select a.company_name,     b.job_id,     b.job_name,     c.user_name,     d.comments,     d.reliability,     d.rating company a, usercompjobrating e, jobs b inner join users c    on c.users_id = e.users_id inner join company_job f     on e.comp_job_id = f.comp_job_id inner join ratings d     on d.rating_id = e.rating_id; 

i'm assuming im close, way off @ same time?

ill try give more information:

usercompjobrating has primary key ucjr_id , 3 foreign keys of comp_job_id, users_id , rating_id

company_job table primary key comp_job_id, , 2 foreign keys job_id, company_id

ratings table has rating_id primary key , rest rating information

users table has users_id primary key , basic user information address etc etc

jobs table has job_id primary key , basic information job, such name, price, etc.

company table has company_id primary key , basic company information, similar users table.

here definations:

create table `company` (   `company_id` int(11) not null auto_increment,   `company_name` varchar(45) not null,   `caddress` varchar(45) not null,   `ctown` varchar(45) not null,   `cpostcode` varchar(12) not null,   `ctelephone` varchar(45) not null,   primary key (`company_id`) ) engine=innodb auto_increment=3 default charset=latin1  create table `company_job` (   `comp_job_id` int(11) not null auto_increment,   `company_id` int(11) not null,   `job_id` int(11) not null,   primary key (`comp_job_id`),   key `company_id_idx` (`company_id`),   key `job_id_idx` (`job_id`),   constraint `company_id` foreign key (`company_id`) references `company` (`company_id`) on delete no action on         update no action,   constraint `job_id` foreign key (`job_id`) references `jobs` (`job_id`) on delete no action on update no     action  create table `jobs` (   `job_id` int(11) not null auto_increment,   `job_name` varchar(45) not null,   `job_cost` varchar(45) default null,   `job_avg_time` varchar(45) default null,   `job_avg_cost` varchar(45) default null,   `job_description` varchar(45) not null,   `company_id` int(11) not null,   primary key (`job_id`) ) engine=innodb auto_increment=4 default charset=latin1 ) engine=innodb auto_increment=4 default charset=latin1  create table `ratings` (   `rating_id` int(11) not null auto_increment,   `comments` varchar(200) default null,   `cost` varchar(45) default null,   `reliability` varchar(45) default null,   `rating` int(11) default null,   primary key (`rating_id`) ) engine=innodb auto_increment=4 default charset=latin1  create table `usercompjobrating` (   `ucjr_id` int(11) not null auto_increment,   `comp_job_id` int(11) default null,   `rating_id` int(11) default null,   `users_id` int(11) default null,   primary key (`ucjr_id`),   key `comp_job_id_idx` (`comp_job_id`),   key `rating_id_idx` (`rating_id`),   key `user_id_idx` (`users_id`),   constraint `comp_job_id` foreign key (`comp_job_id`) references `company_job` (`comp_job_id`) on delete no action on update no action,   constraint `rating_id` foreign key (`rating_id`) references `ratings` (`rating_id`) on delete no action on update no action,   constraint `users_id` foreign key (`users_id`) references `users` (`users_id`) on delete no action on update no action ) engine=innodb auto_increment=4 default charset=latin1  create table `users` (   `users_id` int(11) not null auto_increment,   `user_name` varchar(45) not null,   `uaddress` varchar(45) not null,   `utown` varchar(45) not null,   `upostcode` varchar(45) not null,   `utelephone` varchar(45) not null,   `udob` varchar(45) not null,   primary key (`users_id`) ) engine=innodb auto_increment=3 default charset=latin1 

the query needs i.e. use form

select a.company_name,     f.job_id,     f.job_name,     b.user_name,     c.comments,     c.reliability,     c.rating company     inner join users b        on a.???? = b.??? 

since dont have table definitions cant join conditions. show tables definition , able help.

update:

so based on table structures looking this:

select * company cmp inner join company_job cmpjb     on cmp.company_id = cmpjb.company_id inner join jobs jb     on cmpjb.job_id = jb.job_id inner join usercompjobrating ucmpjbr     on ucmpjbr.comp_job_id = ucmpjbr.comp_job_id inner join users usr      on usr.users_id = ucmpjbr.users_id inner join ratings rat    on rat.rating_id = ucmpjbr.rating_id 

note cannot use folder table in join there no primary/foreign key relationships of other tables folder table.

i suggest dissect query , let me know if need understand details.