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)]

Answer 1

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);