select data by array. sql

Question

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

what is the problem with in clause

singhakash
– singhakash

2015-03-23 07:43:08 +00:00
Commented Mar 23, 2015 at 7:43 — singhakash
– singhakash, Commented Mar 23, 2015 at 7:43
Post your input data and desired output.

Lalit Kumar B
– Lalit Kumar B

2015-03-23 07:43:11 +00:00
Commented Mar 23, 2015 at 7:43 — Lalit Kumar B
– Lalit Kumar B, Commented Mar 23, 2015 at 7:43

Sonnywhite · Accepted Answer · 2015-03-23 08:58:53Z

1

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.

edited Mar 23, 2015 at 8:58

answered Mar 23, 2015 at 8:51

Sonnywhite

13611 bronze badges

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

Comments

Chamara Maduranga · Accepted Answer · 2015-03-23 07:50:02Z

1

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.

answered Mar 23, 2015 at 7:50

Chamara Maduranga

2663 silver badges16 bronze badges

5 Comments

Ales Over a year ago

this doesn't help, I need the the news with 'id0001' and 'id0002' at same time

Chamara Maduranga Over a year ago

ok , "select a.* from " change to "select c.tag_id.a.* from ".

Ales Over a year ago

i try, but it doesn't help

Chamara Maduranga Over a year ago

what is your expectation result ? can you give a example ?

Ales Over a year ago

there is some example in a question.

codework · Accepted Answer · 2015-03-23 07:52:52Z

0

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.

answered Mar 23, 2015 at 7:52

codework

668 bronze badges

1 Comment

Ales Over a year ago

the same problem, this doesn't work I need the the news with 'iname1 and name2' at same time

Rajesh · Accepted Answer · 2015-03-23 09:21:01Z

0

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)

answered Mar 23, 2015 at 9:21

Rajesh

3944 silver badges14 bronze badges

Comments

psaraj12 · Accepted Answer · 2015-03-23 13:56:52Z

0

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)

edited Mar 23, 2015 at 13:56

answered Mar 23, 2015 at 9:41

psaraj12

5,0783 gold badges25 silver badges34 bronze badges

Collectives™ on Stack Overflow

select data by array. sql

5 Answers 5

Comments

5 Comments

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

5 Comments

1 Comment

Comments

Comments

Related