CODEWITHSUNDEEP
mysqlsql
Camillo
Camillo

Reputation: 35

SQL many-to-many relationship with Join and Where clause

I'm trying to build a many-to-many SQL query, and then join the results with other tables.

I have 5 tables: articles, hashtags, articles_hashtags (which maps hashtags.id and articles.id), users, authors.

If I want to query all articles and get author and user info together, I use this query, and it works:

SELECT articles.*, authors.name as authorName, users.name as userName
    FROM articles
    LEFT JOIN authors ON articles.authorId = authors.id
    LEFT JOIN users ON articles.userId = users.id

Otherwise if I try to query all articles with a certain hashtag, I use this (as found out here):

SELECT articles.*
    FROM articles, hashtags, articles_hashtags
    WHERE hashtags.id = articles_hashtags.hashtagId
    AND (hashtags.name IN ('prova'))
    AND articles.id = articles_hashtags.articleId
    GROUP BY articles.id

But on this second one how can I join the informations from the other tables (users and authors)?

Thanks!

Upvotes: 0

Views: 941

Answers (1)

Khurram Ali
Khurram Ali

Reputation: 1679

May be you are looking for this

I assume that you are looking for all matching records 

    SELECT art.*
    FROM articles art
    INNER JOIN articles_hashtags arc_hs ON art.id = arc_hs.articleId
    INNER JOIN hashtags hs on hs.id = arc_hs.hashtagId
    INNER JOIN authors aut ON art.authorId = aut.id
    INNER JOIN users u ON art.userId = u.id
    WHERE hs.name IN ('prova')
    GROUP BY art.id

If you want all information of articals regardless of having matching records in other tables then you can use left join

  SELECT art.*
    FROM articles art
    LEFT JOIN articles_hashtags arc_hs ON art.id = arc_hs.articleId
    LEFT JOIN hashtags hs on hs.id = arc_hs.hashtagId
    LEFT JOIN authors aut ON art.authorId = aut.id
    LEFT JOIN users u ON art.userId = u.id
    WHERE hs.name IN ('prova')
    GROUP BY art.id

Note: You are using CARTESIAN JOIN in your second query which is very bad

Upvotes: 1

Related Questions