Searching and Joining multiple tables

[anjerodesu]

Hi everyone, I would like to ask a question about MySQL. Say, I have a database like this:

database 1:
articles
|- id
|- title
|- description

database 2:
tags
|- id
|- articleID
|- tag

I needed to search from articles.description and tags.tag and while doing so, the searched articles should get the tag from the tags table and the searched tags should get the description from the arcticles.

displaying them as:
title | description | tags

I'm not really sure how to do this yet as I am not an expert when it comes to MySQL. An answer will be greatly appreciated.

TIA

[rch1231]

Hello,

You need a join for this but getting all of the tags on the same line could be tricky. Start with something like this:

select articles.title, 
articles.descripton,
tags.tag
from articles
left join tags on articles.id = tages.articleID

This will give you every record from articles (even those without an entry in the tags table) and all matching records from the tags table.

[adam_k]

Read here: http://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/

[anjerodesu]

I tried it by using:
SELECT articles.* , tags.articleID, tags.tag FROM articles, tags WHERE articles.description LIKE "%sample%" OR articles.title LIKE "%sample%" OR tags.tag LIKE "%sample%" GROUP BY articles.id
but the problem is that the tag is not the right one for the article.

The results are:
title 1 | description 1 | tags 3
title 2 | description 2 | tags 3
title 3 | description 3 | tags 3

Is there a way that it will return the right tag for the right article?

[anjerodesu]

Yeah, I'm a bit of a noob-like stupid with MySQL. Anyway, adding LEFT JOIN solved the problem.

SELECT articles.* , tags.articleID, tags.tag
FROM articles
LEFT JOIN tags
ON articles.id = tags.image_id
WHERE articles.description LIKE "%sample%"
OR articles.title LIKE "%sample%"
OR tags.tag LIKE "%sample%"
GROUP BY articles.id

It may help somebody in the future. :)