查询从多个表获取值。

I have a table video with fields videoid, genre(int-foreign key), language(int-foreign key) etc. I want to get the value of genre from genre table and language from movie_languages table.

我有一个带有字段视频、类型(int-foreign key)、语言(int-foreign key)等的表格视频，我想从类型表和movie_language表的语言中获得类型的价值。

The structure of tables are given below:

表的结构如下:

video

视频

genre

类型

movie_languages

movie_languages

How can I join these 3 tables to get the language and genre related to each videos in video table. Also when user didn't select genre/language in the form, value 0 will be inserted to the table. Will this affect the query. I am using codeingiter and I tried with the following query and is not working.

如何加入这3个表，以获得与视频表中每个视频相关的语言和类型。当用户没有在表单中选择类型/语言时，值0将被插入到表中。这会影响查询。我正在使用codeingiter，我尝试了下面的查询并没有工作。

$this->db->select('video.*,movie_languages.language,genre.genre');    
$this->db->join('genre', 'video.genre = genre.id');
$this->db->join('movie_languages', 'video.language = movie_languages.id');
$query = $this->db->get();

Please help me.

请帮助我。

Thanks in advance.

提前谢谢。

2 个解决方案

#1

It will be, you need a left join in this case

在这个例子中，你需要一个左连接。

Try this

试试这个

$query = $this->db
->select('v.*,ml.language,g.genre')
->from('video as v')
->join('movie_languages AS ml', 'v.language = ml.id', 'left outer')
->join('genre AS g', 'v.genre = g.id', 'left outer')
->get(); 

#2

try this

试试这个

$this->db->select('video.*,movie_languages.language,genre.genre') 
         ->from('video')
         ->join('genre', 'video.genre = genre.id')
         ->join('movie_languages', 'video.language = movie_languages.id');
$query = $this->db->get();