Select multiple tables

Question

This is a part of my DB structure that I want to query:

DB structure

The relations are these:

article.art_author - user.usr_id
article.art_id - tag_article.rel_art_id
tag_article.rel_tag_id - tag.tag_id

I want to select the articles that are written from selected users (by usr_id) OR the article that have the selected tags (by tag_id) in one query if possible.

I have tried this but does not give me the desired result:

SELECT * FROM 
                ((article JOIN user on article.art_author = user.usr_id)
                JOIN
                tag_article on article.art_id = tag_article.rel_art_id)
                JOIN
                tag on tag_article.rel_tag_id = tag.tag_id
                WHERE
                article.art_lang = '$cur_lang'
                $sql_in
                ORDER BY
                article.art_date desc
                LIMIT $first_record, $range

xlecoustillier · Accepted Answer · 2012-11-22 20:28:18Z

1

select distinct a.art_id
from article a,
    user u,
    tag_article ta,
    tag t
where a.art_author=u.user_id
    and ta.rel_art_id = a.art_id
    and ta.rel_tag_id = t.tag_id
    and (u.usr_id in (<your selected users>) or t.tag_id in (<your selected tag>))

I wrote all joints so you can select all columns you want, but it can be done more rapidly if you just need article data :

select a.art_id
    from article a,
    tag_article ta
where a.art_id=ta.rel_art_id
    and (a.art_author in (<your selected users>) or ta.rel_tag_id in (<your selected tags>))

answered Nov 22, 2012 at 20:28

xlecoustillier

16.4k15 gold badges64 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

kapantzak Over a year ago

Thank but it's not working! Even if I use SELECT DISTINCT gives me an article twice..

kapantzak Over a year ago

Yes that's what's happening. Thank again for your time!

kapantzak Over a year ago

What can I do to get an article that has more than one tags linked to it, only one time?

xlecoustillier Over a year ago

You'll have to avoid displaying fields from tags or tags_article table, or using GROUP_CONCAT and GROUP BY commands.

Collectives™ on Stack Overflow

Select multiple tables

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related