Problem with JOINS

Question

I have a problem with my database or query. I've three tables, one for videos, another for tags of the videos and other for the tags descriptions and theirs id.

I want to access to the TAG_DES.

For example:

In my table tags I have: tag_id=3 l tag_des=music

In my table tag_media I have: tag_id=3 l media_id=5

(it means that the video with id=5 have a tag with id=3, that is music)

So I want to get tag_des (music for example) where media_id=5 by using te tag_id...

This is the image of the tables

This is the query that I have, but it returns me null i dont know why:

SELECT t.tag_des 
FROM media_thumbnail AS m 
    INNER JOIN tag_media as tm 
        ON tm.media_id=5
    INNER JOIN tags AS t 
        ON tm.tag_id=5

Answer 1

You should use foreign keys and primary keys to join the tables, and then use WHERE clause to filter rows:

SELECT     t.tag_des 
FROM       media_thumbnail AS m 
INNER JOIN tag_media as tm 
ON         tm.media_id = m.media_id
INNER JOIN tags AS t 
ON         tm.tag_id = t.tag_id
WHERE      m.media_id = 5;

Postgres allows to use USING (join column list) instead of ON:

Quoted from docs:

The USING clause is a shorthand that allows you to take advantage of the specific situation where both sides of the join use the same name for the joining column(s). It takes a comma-separated list of the shared column names and forms a join condition that includes an equality comparison for each one.

Now you can rewrite your query like this:

SELECT     t.tag_des 
FROM       media_thumbnail AS m 
INNER JOIN tag_media as tm 
USING      (media_id)
INNER JOIN tags AS t 
USING      (tag_id)
WHERE      m.media_id = 5;

Have a look at:

Postgres docs:

• Joins between tables
• Join types

Postgres tutorial:

• JOINS