CODEWITHSUNDEEP
mysqlsql
Marco
Marco

Reputation: 867

mysql query with multiple left join in 3 tables?

i have a search query which will retrieve information from 3 tables i made the query so it retrieve the information from 2 tables and i don't know if i can combine the third one or not

SELECT *
FROM articles
INNER JOIN terms
ON articles.ArticleID = terms.RelatedID AND terms.TermType = 'article'

the third query is 

SELECT * FROM categories where CategoryID in (something)

something is a filed in the articles tables which have value like '3,5,8'

i want do this 2 queries into 1 query and i don't know if it can be done by 1 query or not

Upvotes: 0

Views: 95

Answers (2)

Bryan Allo
Bryan Allo

Reputation: 80

If i understand you correctly. Looks like you have multiple categories for each article with the Category IDs all stored as a concatenated string.

SELECT A.*
FROM articles A
INNER JOIN terms T on A.ArticleID = T.RelatedID AND T.TermType = 'article'
LEFT JOIN categories C on C.CategoryID IN (3,5,8 OR A.CategoryIDs)
GROUP BY C.CategoryName

You want to LEFT JOIN since you may or may not have multiple categories, you can group by Categories to get disticnt category article pairs and CONCAT() to recombine article records as needed.

Upvotes: 0

Geek Num 88
Geek Num 88

Reputation: 5312

without looking at your schema (which would be helpful) and some sample data try this query

SELECT *
FROM categories,articles
INNER JOIN terms
ON (articles.ArticleID = terms.RelatedID AND terms.TermType = 'article')
WHERE
FIND_IN_SET(categories.CategoryID,articles.categories)

here is the definition for FIND_IN_SET()

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

Upvotes: 1

Related Questions