1:n data structure in mysql 5.1.41 wont work

I am working with mysql 5.1.41 on a problem where a user should be able to specify what languages he does speak. This shouldnt be a problem at all but it is.

I thought of a structure like that:

CREATE TABLE `user2lang` (
  `id` int(11) NOT NULL auto_increment,
  `user` int(11) NOT NULL,
  `lang` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) 

and a second table

CREATE TABLE `lang` (
  `id` int(11) NOT NULL auto_increment,
  `langname` varchar(50) NOT NULL,
  PRIMARY KEY  (`id`)
)

In table is a list with languages to chose from e.g.

id : langname 
1  : eng
2  : ger
3  : lat

and in table I store which user does speak what language's id

id : user : lang
1  : 1 : 1 (eng)
2  : 1 : 3 (lat)
3  : 2 : 2 (ger)
4  : 2 : 1 (eng)

Now I want to grab a list of all aviable languages and if a special user (user #2) is able to speak it, it should be marked.

The obvious should be a left join:

SELECT user2lang.lang,user2lang.user
FROM `user2lang` left join lang on
lang.id = user2lang.lang where
user2lang.user=2

All I get are the languages,that user #2 does speak, not all languages with the info 开发者_如何学Cif user #2 does speak it. I am looking for something like:

lang : user 
1    : 2 
2    : 2 
3    : - or NUll or whatever 
4    : -

Just change the join clause and it will work:

SELECT lang.id ,user2lang.user
FROM `lang` left join `user2lang` on
lang.id = user2lang.lang AND user2lang.user=2

Turn the left join around, so that table lang is on the left-hand side of the left join:

SELECT lang.id ,user2lang.user
FROM `lang` left join `user2lang` on
lang.id = user2lang.lang where
user2lang.user=2

Edit

1 -- you need to be sure that your projection is pulling the lang value from the lang table, not from user2lang. So it should be: SELECT lang.id, user2lang.user not SELECT user2lang.lang, user2lang.user.

2 -- if you want all languages to show up, you'll have to include OR user2lang.user is NULL in your where clause. Like this:

SELECT lang.id, lang.langname, user2lang.user 
FROM lang left join user2lang 
  ON user2lang.lang=lang.id 
WHERE user2lang.user=2 or user2lang.user is null;

Edit (Again)

While the above code example does work - @iliyan, actually has the correct answer. The restriction user2lang.user=2 should go into the ON clause. Making the query:

SELECT lang.id, lang.langname, user2lang.user 
FROM lang left join user2lang 
  ON user2lang.lang=lang.id AND user2lang.user=2;

That's a much better approach than including the is null bit I suggested above.

personally i'd change your design dropping the unnecessary surrogate primary key in your user2lang table to the following design which better enforces data integrity (excludes RI):

drop table if exists users;
create table users
(
user_id int unsigned not null auto_increment primary key, -- unsigned pls
username varbinary(32) unique not null
)
engine=innodb;

drop table if exists languages;
create table languages
(
lang_id smallint unsigned not null auto_increment primary key, -- how many languages ?
name varchar(255) unique not null
)
engine=innodb;

drop table if exists user_languages;
create table user_languages
(
user_id int unsigned not null,
lang_id smallint unsigned not null,
primary key (user_id, lang_id) -- clustered composite PK super fast (innodb only)
)
engine=innodb;