MySQL Select from three tables

[eluser]Assim[/eluser]
I want to write a query which is quite difficult.

I have 3 tables:
- tags (columns: TagID)
- tagpages (columns: TagPageID, TagID, PageID)
- tagtext (columns: TagTextID, TagID, LanguageCode, Text)
Columns in italics aren't to be used, they are just for the primary key.

To explain this, each tag will be associated with a TagID, each page will be associated to a TagID from tagpages table, and tagtext holds the tag translation for each language.

Now I want to write a SELECT statement to get all tags for a particular page but written in a specific language. The LanguageCode column is equal to $language

Here's what I thought of doing:
// Get all tag IDs of a page
SELECT TagID FROM tagpages WHERE PageID = '$page_id'

// This will bring the tag translation of a tag for a page
SELECT Text FROM tagtext WHERE LanguageCode = '$language' WHERE TagID = '$tag_id'

How do I get all the tags for a particular page and show the text from the selected language?

Or maybe we could use a foreach function after we execute the first query? Any ideas?

[eluser]Christoffer[/eluser]

Code:

<?php
  $result = $this->db->select('*')->
    from('tags t')->
    join('tagpages tp', 'tp.TagID=t.TagID', 'inner')->
    join('tagtext tt', 'tt.TagID=t.TagID', 'inner')->
    where('tp.PageID', $page_id)->
    where('tt.LanguageCode', $language)->
    get()->
    result();
?>

[eluser]Assim[/eluser]
That's in Active Record, could you please explain about it a bit? Or maybe if you don't mind if you could write it in the normal syntax because I'm not familiar with Active Record.

[eluser]Christoffer[/eluser]
Well, it basically selects all info that the three tables have in common where PageID == $page_id and LanguageCode == $language.

ActiveRecord has many advantages and you might want to learn more about it... But if you don't want to here's regular SQL.

Code:

SELECT * FROM tags t
INNER JOIN tagpages tp ON tp.TagID=t.TagID
INNER JOIN tagtext tt ON tt.TagID=t.TagID
WHERE tp.PageID = $page_id AND tt.LanguageCode = $language

[eluser]Assim[/eluser]
[quote author="Christoffer" date="1266872450"]Well, it basically selects all info that the three tables have in common where PageID == $page_id and LanguageCode == $language.

ActiveRecord has many advantages and you might want to learn more about it... But if you don't want to here's regular SQL.

Code:

SELECT * FROM tags t
INNER JOIN tagpages tp ON tp.TagID=t.TagID
INNER JOIN tagtext tt ON tt.TagID=t.TagID
WHERE tp.PageID = $page_id AND tt.LanguageCode = $language

[/quote]
Great. I will probably learn Active Record some time in the future but for now I since I never used JOIN for MySQL, I just wanted to know how can I display tt.Text? Give me a simple example in controller and views if you could.

Thanks in advance.

[eluser]Christoffer[/eluser]
Sure, a sql join is very cool. It allows you to select stuff from more than one table that's related in some way. These three tables share TagID and can then be joined - so with one query to the database you'll get everything from all three tables that share TagID, neat huh?

So if you want to display tt.Text just call for it, it's there now. Do what you always do with an SQL statement, it'll work.

Code:

// Say you have a $result variable in the form of an array
echo $result['Text'];

The "tt" part is just an alias that I used in the join to shorten the code a bit.

[eluser]Assim[/eluser]
Thanks mate. Exactly what I wanted.