CODEWITHSUNDEEP
javasqloracle-database
Ales
Ales

Reputation: 180

select data by array. sql

I have three tables: NEWS 

news_id | other fields..

TAGS

tag_id | other fields..

and connecting table TAGS_NEWS

tag_id | news_id

I want to select data from NEWS table by tags. But the problem is that I should select data by many tags. I can create only one solution of this problem, at first select data by one tag, than from selected data by another tag and so on. But I think it isn't good way of solution of this problem. Maybe is the best way to solve this problem? Maybe I can select necessary data in one query?

For example, NEWS_TAG table: 

tag_id | news_id
    1   1
    3   12
    4   11
    1   10
    6   1
    7   2
    8   3
    9   3
    10  3

Select data by tags 1,6 Get the result: news_id = 1, or Select data by tags 8,9 Get the result: news_id = 3

Upvotes: 0

Views: 101

Answers (5)

psaraj12
psaraj12

Reputation: 5072

You can generalize sonnywhite solution like the below

with tag_ids as ( select tag_id from tags where tag_id in (1,6)),
tag_id_cnt as ( select count(1) cnt from tag_ids)
 select * from news,tag_id_cnt where (news_Id,cnt) in 
 (select news_Id, count(1) from news_tags a, tag_ids b
Where a.tag_id=b.tag_Id
Group by news_Id)

Upvotes: 0

Rajesh
Rajesh

Reputation: 384

Please try this.

select news_id from news n join tags_news tn on n.news_id=tn.news_id where tn.tag_id in(1,3,6)

Upvotes: 0

Sonnywhite
Sonnywhite

Reputation: 136

I think I got your problem and I had to solve a similar problem.

Try this solution:

select *
from news
join news_tags on news_tags.news_id = news.id
where news_tags.tags_id in(1,2)
group by news.id
having count(*) = 2

The only things you have to transmit to this query are the set of tags_id's (in my example 1,2) and the number of tags_id's (in my example 2).

I've tested it with SQL Fiddle.

Upvotes: 1

codework
codework

Reputation: 66

select * from news where new_id in 
(select news_id from tagnews where tag_id in 
(select tag_id from tag where tag_name in (name1,name2)) )

maybe it can help.

Upvotes: 0

Chamara Maduranga
Chamara Maduranga

Reputation: 256

try this solution 

select a.* from  NEWS a ,TAGS_NEWS b,TAGS c
where a.news_id = b.news_id
and b.tag_id = c.tag_id
and c.tag_id in ('id0001','id0002')

you need to pass only tags ids which is you need.

Upvotes: 1

Related Questions