CODEWITHSUNDEEP
mysql
sehummel
sehummel

Reputation: 5568

MySQL join statement

I need to join two tables:

countries table

country|lang1|lang2|lang3|lang4
Canada |1    |2    |NULL |NULL
US     |1    |NULL |NULL |NULL

and

languages table

id|language
1 |English
2 |French

I want to return the values for both rows in my countries table, with the language names.

How do I do that?

I tried: 

SELECT * FROM en_countries LEFT JOIN all_languages 
ON (all_languages.id = en_countries.lang1 AND all_languages.id = en_countries.lang2)

but that doesn't give me the language names. How do I do this?

If someone could give me this in Active Record format, that would be a bonus. I'm doing this for CodeIgniter.

Upvotes: 0

Views: 202

Answers (4)

David
David

Reputation: 5456

You need to select from all_languages two times

SELECT en_countries.country, t1.language as lang1, t2.language as lang2    FROM en_countries    
JOIN all_languages AS t1 ON t1.id = en_countries.lang1 
JOIN all_languages AS t2 ON t2.id = en_countries.lang2

Then I believe you access in php as $row->lang1

Upvotes: 0

Denis de Bernardy
Denis de Bernardy

Reputation: 78413

I'm not sure I'm getting your question right, but it sounds like you should be using an inner join rather than a left join. See this.

select country.*
from en_countries as country
join all_languages as lang1
on lang1.id = country.lang1
join all_languages as lang2
on lang2.id = country.lang2
-- optionally:
where lang1.id = ?
and lang2.id = ?

Upvotes: 1

a1ex07
a1ex07

Reputation: 37354

First of all, you use AND that means that only records with the same lang1 equals lang2 will joined. I believe you need something like :

SELECT c.*, l1.language as lang1_name, l2.language as lang2_name  
FROM en_countries c
LEFT JOIN all_languages l1 ON (l1.id = c1.lang1)
LEFT JOIN all_languages l2 ON (l2.id = c1.lang2)

Upvotes: 1

Lourens
Lourens

Reputation: 1518

I think the issue here is with (all_languages.id = en_countries.lang1 AND all_languages.id = en_countries.lang2)

rather try 

SELECT * FROM en_countries LEFT JOIN all_languages 
ON (all_languages.id = en_countries.lang1 OR all_languages.id = en_countries.lang2)

The 'and' implies that one language should be the first and second language of a country, replacing it with 'or' may do the trick

Upvotes: 1

Related Questions