mySQL query - Use results again in same query

Question

I actually don't have any code yet to provide..but I can give you the data I am trying to manipulate.

I am working with a set of tags/keywords. Keywords can be related to another via the 'related_id' column.

So my table looks like:

keyword_tbl: keyword_id | keyword | related_id

For this example, lets imagine the table is populated with the following entries

Entry 1:
keyword_id : 1
keyword: Marathons
related_id: 0

Entry 2:
keyword_id : 2
keyword: Boston
related_id: 1

As you can see, this entry of Boston, is related to Marathons via the related_id

I am working on giving the user the ability to search. If they search for an individual term, thats easy and not the question. However, if they search for "Boston Marathon," I now am having difficulty with the query.

SELECT * FROM keyword WHERE keyword LIKE "%boston%" OR keyword LIKE "%marathon%"

After this initial query, i'd like to compare the results, which would be the 2 entries I detailed above.

Id like to return only the term that is related to the other. In this case, Boston is the 'lowest' common denominator, and thus, I'd like to return it.

Imagine: Marathons -> Boston

Can this be done in a single query?

Thanks!

@goodmood The result would be the entirety of the row for keyword_id 2 (boston). This would be due to the fact that the related_id of keyword: Boston is related to keyword:Marathons and not the other way around. — Ricky Mason
– Ricky Mason, Commented Jan 8, 2013 at 19:13
Its not necessary. Down the road it might get more complex, but that functionality isn't needed now. — Ricky Mason
– Ricky Mason, Commented Jan 8, 2013 at 19:19

Chamila Chulatunga · Accepted Answer · 2013-01-08 19:16:26Z

1

I'm thinking something like this might do the trick:

SELECT
  a.*
FROM keyword a
JOIN keyword b
ON (a.related_id = b.keyword_id)
WHERE (a.keyword LIKE "%boston%"
       OR a.keyword LIKE "%marathon%")
AND (b.keyword LIKE "%boston%"
       OR b.keyword LIKE "%marathon%")

answered Jan 8, 2013 at 19:16

Chamila Chulatunga

4,95418 silver badges17 bronze badges

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

5 Comments

Ricky Mason Over a year ago

this appears to do what I need it to. Thank you for the solution! I assume this could be modified for more than 2 keywords as well?

Chamila Chulatunga Over a year ago

It might start getting messy, but yes, with more keywords you would have more OR ... LIKE ... expressions within the WHERE clause

Ricky Mason Over a year ago

This might be asking for a lot, but would you be able to add an additional example that uses 3 keywords? So i know how to build the query variables in php? Im pretty new to sql :X

Chamila Chulatunga Over a year ago

Probably need a concrete example - not sure what you would want returned in that case - anything that matches, or still only one based on a chain of relations?

Ricky Mason Over a year ago

good point. It's getting complex now. The trick is figuring out how the user could screw up the search, isn't it? Also, assuming that you might not have either(any) of the words they are querying. Is it possible to check the relation between all results, and return all the lowest common denominators?

echo_Me · Accepted Answer · 2013-01-08 19:17:27Z

0

The below query will give you the answer Marathons -> Boston. IF there is a keyword that does not have a relation it will be displayed as IceCream ->

SELECT resultset1.keyword,'->',IF(resultset2.keyword IS NOT NULL,resultset2.keyword,'')
FROM
    (SELECT * FROM keyword WHERE keyword LIKE "%boston%" OR keyword LIKE "%marathon%")
     as resultset1
LEFT JOIN
    (SELECT * FROM keyword WHERE keyword LIKE "%boston%" OR keyword LIKE "%marathon%")
as resultset2
on resultset1.keyword_id=resultset2.related_id;

edited Jan 8, 2013 at 19:17

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

answered Jan 8, 2013 at 19:16

user1480933

211 bronze badge

1 Comment

Ricky Mason Over a year ago

Thanks, this is interesting, I'll have to play around with this one.

Collectives™ on Stack Overflow

mySQL query - Use results again in same query

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related