Subquery, temporary table, mysql variables, or procedure to get pos and neg search terms from a column in one table to query another

I am querying a table of deal titles by a set of adventure keyword terms and tagging the related adventure when one of the terms associated with the adventure is in the title. To match multiple adventures with multiple deals in the least amount of queries I have broken this down into two steps:

1. I created a temporary table to join all of the search terms for each adventure in one column concatenated by "|" so I can use it in a regexp.

2. join this temporary table with the deals table on deals.title regexp temp.concatenated search terms.

This works great, but I have two questions:

1. Can I do this in a subquery without creating a temporary table?

2. I would like to introduce a status to my search terms table that classifies the search terms as negative or positive. Then I would like to search the deal titles and return matches only when one or more of the positive terms are in the title AND NONE of the negative terms are in the title. I can't figure out how to get the results of two different kinds of status from the same table and then use the results to join/query another table. How do I do this?? I haven't used variables or stored procedures with sql yet, but it seems like this may be the way to go?

Here is the first part of the code.

I have a table of search terms associated with an adventure:

search_terms
id | adventure_id | term
130     124     kitesurf    
131     124     kiteboard   
132     124     kite surf   
133     124     kite-surf   
134     124     kite board  
135   开发者_如何转开发  125     water ski
    135     125     waterski


adventures
id | activity
124  Kite boarding
125  Waterskiing

and a table of deals associated with businesses

deals
id | title | biz_id ...
3    50% off waterskiing on the lake 10

The SQL:

create temporary table tmp1 as
select adv.activity,st.adventure_id, GROUP_CONCAT(st.term SEPARATOR '|') as `match`
FROM (search_terms st)
JOIN adventures adv ON adv.id=st.adventure_id
GROUP BY adv.id;

select d.biz_id, temp.adventure_id, d.id as deal_id
from daily_deals d
JOIN tmp1 temp ON d.title REGEXP temp.match;

Now to get to the second question. I have added a 'status' column to the search_terms table so search_terms

id | adventure_id | term | status (yes or no)

and basically I want to be able to say GROUP_CONCAT(st.term that are status=yes SEPARATOR '|') as **yes** and GROUP_CONCAT(st.term (that are status=no) SEPARATOR '|') as **no** and in the temp table then do

select d.biz_id, temp.adventure_id, d.id as deal_id
        from daily_deals d
        JOIN tmp1 temp ON d.title REGEXP temp.yes AND NOT_REGEXP temp.no;

How can I get to this kind of result? Thanks for any help, it's my first time using SO.

1. Can I do this in a subquery without creating a temporary table?

Generally you can replace a temp table with a derived query for example

SELECT d.biz_id, 
       TEMP.adventure_id, 
       d.id AS deal_id 
FROM   daily_deals d 
       JOIN (SELECT adv.activity, 
                    st.adventure_id, 
                    GROUP_CONCAT(st.term SEPARATOR '|') AS `match` 
             FROM   search_terms st 
                    JOIN adventures adv 
                      ON adv.id = st.adventure_id 
             GROUP  BY adv.id) TEMP 
         ON d.title REGEXP TEMP.MATCH; 

"I want to be able to say GROUP_CONCAT(st.term that are status=yes SEPARATOR '|') as yes and GROUP_CONCAT(st.term (that are status=no) SEPARATOR '|') as no"

    SELECT d.title, matches.match, matches.match_neg 
    FROM   daily_deals d 
    JOIN (Select * from (SELECT  
                adventure_id, 
                GROUP_CONCAT(term SEPARATOR '|') AS `match` 
         FROM  search_terms 
         where status = 'yes'
         GROUP  BY adventure_id) as temp1
    left outer join
    (SELECT  
                adventure_id as adventure_id_neg, 
                GROUP_CONCAT(term SEPARATOR '|') AS `match_neg` 
         FROM  search_terms 
         where status = 'no'
         GROUP  BY adventure_id) as temp2
    on temp1.adventure_id = temp2.adventure_id_neg) as matches
    ON d.title REGEXP matches.match AND d.title REGEXP matches.match_neg