Query for getting values from multiple tables

Question

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.

The structure of tables are given below:

video

enter image description here

genre

enter image description here

movie_languages

enter image description here

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.

$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.

have you tried? : this page

Louis Milotte
– Louis Milotte

2014-03-20 06:52:46 +00:00
Commented Mar 20, 2014 at 6:52 — Louis Milotte
– Louis Milotte, Commented Mar 20, 2014 at 6:52

Arek S · Accepted Answer · 2014-03-20 06:53:24Z

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

answered Mar 20, 2014 at 6:53

Arek S

4,7794 gold badges27 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dexter · Accepted Answer · 2014-03-20 06:53:24Z

1

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

answered Mar 20, 2014 at 6:53

Dexter

1,7964 gold badges27 silver badges55 bronze badges

3 Comments

Jenz Over a year ago

thanks for your help. But this query will not show those records where genre/language are 0.

Dexter Over a year ago

you can add where condition ->where('genre',0)

Arek S Over a year ago

@MohitJain you need a left join in this case. Have a look at my answer

Collectives™ on Stack Overflow

Query for getting values from multiple tables

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related