CODEWITHSUNDEEP
mysqlsqljoinsql-like
Hailwood
Hailwood

Reputation: 92691

Using a second table as a lookup?

I have Three tables,

Posts, Tags, Posts_Tags_Link

Posts has: id, content

Tags has: id, tag

Posts_Tags_Link has: post_id, tag_id

Basically if a tag is linked to a post then an entry is created in Posts_Tags_Link as this is a many-many relationship.

Anyway, I want to do some searches and return all rows from Posts that are linked to a particular keyword.

E.g. If I have the

Posts:

id | content
 1 | some stuff
 2 | more stuff
 3 | stuff again

Tags:

id | tag
 1 | first
 2 | second
 3 | third
 4 | fourth

Posts_Tags_Link

post_id | tag_id
      1 | 1
      1 | 2
      2 | 2
      3 | 3
      3 | 4

and I search for second I want to return

id | content
 1 | some stuff
 2 | more stuff

I assume I am to use a join for this, Would I just join my posts table to the link table, on the post_id and join the link table to the link table to the tags table on the tag_id column?

I believe that is right, but If I only want to rows that match the search (like not where) would I use like or would one of the different joins work?

I want that if I search for sec it would have the same result as if I searched for second so believe that I have to do this using like?

Upvotes: 2

Views: 83

Answers (2)

Nitesh Kumar
Nitesh Kumar

Reputation: 1774

Try to use the following query.

SELECT p.id, p.content FROM
Posts_Tags_Link ptl 
    INNER JOIN Posts p ON p.id = ptl.post_id
    INNER JOIN Tags t ON t.id = ptl.tag_id
WHERE t.tag = 'second'

Upvotes: 0

John Woo
John Woo

Reputation: 263893

You should join the three tables since you want to search from them, example

SELECT  a.*
FROM    post a
            INNER JOIN Posts_Tags_Link b
                on a.id = b.post_id 
            INNER JOIN Tag c
                ON b.tag_tag_id = id
WHERE   a.content like '%keyword%' OR     -- build you conditions here
        c.tag like '%keyword%'

Upvotes: 3

Related Questions