Page 1 of 1

Select with Multiple Conditions

Posted: Mon Mar 03, 2008 10:15 pm
by parka
I've a table called tag_log.

In this table are two columns called tag_id and sub_id. This table is used to link tag names to stories.

I'm trying to write the query for a search. The search is looking for all the sub_id that matches the tag_id given.

Example of what I have currently:

Code: Select all

select distinct(sub_id) from tag_log where tag_id in(101, 102, 103);
In the above example, it was performing a union instead of what I really want. It was returning results that have either one or all of the tags.

What's required is for the story to have ALL the three tags. It's should be like a typical search, where the more search terms you enter, the more focused the results will be.

Re: Select with Multiple Conditions

Posted: Mon Mar 03, 2008 11:22 pm
by parka
Here's what I've got after some thinking. It did get me the records I wanted
Is this optimized?

Code: Select all

 
select a.sub_id from tag_log a, tag_log b, tag_log c
where
a.tag_id = 101 and 
b.tag_id = 102 and
c.tag_id = 103 and
a.sub_id = b.sub_id and
b.sub_id = c.sub_id;