mysql - Displaying unwanted repeat results in one-to-many relationship

Question

I have 3 tables.

First is a Company Table: Company_ID, Company_name etc

2nd is a Jobs Table: JOB_ID, Job_Name, Job_description etc

3rd is a Comp_Jobs table: Comp_Job_ID, Company_ID, Job_ID

I am trying to run a query to create a join with these tables so that it will display all the jobs assoicated with a company. eg ive got 2 jobs for company 1 i want to display. but when i run this query:

Select Company.Company_name, Jobs.Job_ID, Company.CAddress, 
Jobs.Job_Name, Jobs.Job_Description from Company, jobs
join company_job where Company.Company_ID = Company_Job.Company_ID
order by Company_job.Job_ID;

it would repeat the result, as in company 1 will display jobs 1 and 2 twice.

any tekkers?

tried your group by, but in the jobs table there will be abit of data redundancy due to the likes of a 'repair' being a different price for a different company. but thanks for the input — Andrew Glass, Jul 23 '13 at 14:26

score 1 · Accepted Answer · answered Jul 23 '13 at 13:45

1

Select Company.Company_name, Jobs.Job_ID, Company.CAddress, 
Jobs.Job_Name, Jobs.Job_Description
from Company
join company_job on Company.Company_ID = Company_Job.Company_ID
join jobs on jobs.Job_ID =  Company_Job.job_id
order by Company_job.Job_ID;

Try this. Not sure if this is what you asked for
There was no proper join condition with Company and jobs

answered Jul 23 '13 at 13:45

Akhil

2,602
23
36

yes thats the ticket there, thanks. didnt think there would have to be two joins, suppose makes sense when think about it – Andrew Glass Jul 23 '13 at 13:51
1

@Andrew - NOTE, that your original query also has two joins. the comma is like JOIN if you don't specify a JOIN condition – luksch Jul 23 '13 at 13:53
@luksch where do you mean when you mention the comma just so i know for the future to look out for it. do you mean when i was doing the "from company, jobs"? – Andrew Glass Jul 24 '13 at 03:23
yes exactly. have a look here for a discussion of the matter: http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins – luksch Jul 24 '13 at 08:09

score 1 · Answer 2 · answered Jul 23 '13 at 13:49

You should use INNER JOIN to only display the rows you want:

SELECT Company.Company_name, Jobs.Job_ID, 
       Company.CAddress, Jobs.Job_Name, Jobs.Job_Description
FROM Comp_Jobs
INNER JOIN Jobs ON Jobs.Job_ID = Comp_Jobs.Job_ID
INNER JOIN Company ON Company.Company_ID = Comp_Jobs.Company_ID    
ORDER BY Company_job.Job_ID;

mysql - Displaying unwanted repeat results in one-to-many relationship

2 Answers2