0

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

enter image description here

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
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:

Postgres tutorial:

|improve this answer|||||
  • You can using USING () here to clean this up more. – Evan Carroll Apr 14 '18 at 16:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.