CODEWITHSUNDEEP
mysql
Joshua Bakker
Joshua Bakker

Reputation: 2358

One-to-many relationship MySQL - get only one many-record

I got 2 tables, one of my table looks like this (example):

tbl1
-------------
id int PK
name string

The other one looks like:

tbl2
-------------
type int PK FK (tbl1.id)
langid int PK
content string

So what I want: I want to get all rows from tbl1 joined together with results from tbl2. But what I don't want is for every langid add a result to the join. So if tbl1 contains this data:

id: 1, name: test1
id: 2, name: test2

And tbl2 contains this data:

type: 1, langid: 1, content: testcontent
type: 1, langid: 2, content: testcontent2
type: 2, langid: 3, content: testcontent3

I want only the following data:

tbl1.id: 1, tbl1.name: test1, tbl2.type: 1, tbl2.langid: 1, tbl2.content: testcontent
tbl1.id: 2, tbl1.name: test2, tbl2.type: 2, tbl2.langid: 3, tbl2.content: testcontent3

So it has to get one result with the langid that exist. Hopefully I explained it well. I tried this:

SELECT * FROM `tbl1` INNER JOIN `tbl2` ON (`tbl1`.`id` = `tbl2`.`type`) WHERE `tbl2`.`langid` = 1

But sometimes langid 1 doesn't exist, and just langid 2 by that header. I need to get the tbl1 row with one result from tbl2.

Upvotes: 1

Views: 133

Answers (2)

zipzit
zipzit

Reputation: 3997

Original submission (just gets first response... but that's not what you want...)

SELECT * FROM `tbl1` INNER JOIN `tbl2` ON (`tbl1`.`id` = `tbl2`.`type`) WHERE 1 LIMIT 1

Update -- I think this is what you want. This works on the SQLfiddle 

select *
  FROM `tbl1` INNER JOIN `tbl2` 
  ON ( `tbl1`.`id` = `tbl2`.`type`) 
  Group by tbl1.id

Response: 

id  name    type    langid  content
1   test1     1       1    content1
2   test2     2       3    content3

Upvotes: 1

Bohemian
Bohemian

Reputation: 424993

I think you want a LEFT JOIN, with the condition on the other table in the join condition:

SELECT *
FROM `tbl1` 
LEFT JOIN `tbl2` ON `tbl1`.`id` = `tbl2`.`type`
  AND `tbl2`.`langid` = 1

This will give you every row from tbl1 once each, with data from tbl2 only if it exists with langid 1.

Upvotes: 0

Related Questions