one to one result in mysql Query

Question

I have two table:

career
career_details

Table "career" contain fields (careerID,job_code)
Table "career_details" contain fields (id, careerID, name)

Let the values be [(1,code1),(2,code2)]--- career(table)
Let the values be

[

 ('1','1','codename1'),  
   ('2','1','codename11'),  
   ('3','2','codename22'),  
   ('4','2','codename222')


]

-- career_details(table)

Now if I write the query("SELECT * FROM career c LEFT JOIN career_details cd ON c.career_id=cd.career_id WHERE 1"), then it will give the result 4 rows but I need the result 2 rows only i.e

[(1,code1,1,1,codename1),(2,code2,3,2,codename22)]

possible duplicate of [Limit results from joined table to one row](http://stackoverflow.com/questions/2978726/limit-results-from-joined-table-to-one-row) — Phil, Jul 18 '13 at 06:26
This appears to be a `greatest-n-per-group` problem. Added a relevant tag. As a matter of fact, there are many [SO questions tagged `mysql` and `greatest-n-per-group`](http://stackoverflow.com/questions/tagged/greatest-n-per-group+mysql), some of them having accepted answers too. — Andriy M, Jul 18 '13 at 09:30

Krishna Rani Sahoo · Accepted Answer · 2013-07-19T09:38:46.803

0

Instead of writing left join, you should write inner join

Again as per your requirement, you need the first career details data that is first assigned with a career data. Please refer below link. http://www.sqlfiddle.com/#!2/d1c64/14

SELECT c.id,c.name as name ,cd.id as cdl_id,cd.career_id,cd.name as dtl_name FROM career_details cd  INNER JOIN careers c  ON c.id=cd.career_id
and cd.id in (select max(id) from career_details group by career_id);

edited Jul 19 '13 at 09:38

answered Jul 18 '13 at 09:04

Krishna Rani Sahoo

1,539
1
14
25

I updated my query. Please look into the sqlfiddle http://www.sqlfiddle.com/#!2/d1c64/14 – Krishna Rani Sahoo Jul 19 '13 at 09:40

score 0 · Answer 2 · answered Jul 18 '13 at 12:32

0

SELECT * FROM career c LEFT JOIN career_details cd ON c.career_id=cd.career_id 
group by c.id

Group by c.id will limit only one entry for a c.id in the result set.

answered Jul 18 '13 at 12:32

Praveen Prasannan

7,093
10
50
70

one to one result in mysql Query

2 Answers2