find likewise data from two tables in mysql

Question

i have two tables in my database one is A other one is B A is having few fields in which three are id,name,group B is having feilds like id,title,description, etc.

i have to search the id's of title and description that are having data similar to table A's name or group and then have to insert the id's in a field of table A.

For example, if A is having 'Anna' in its name and 'girl' in its group then i have to search the title's and descriptions in table B that are containing this word 'Anna' or 'girl'.

I want to do this in one single query.

How can i do so?

Edit: Iam explainng my tables here for a better understanding

 table A

id     name     group     matched_id
1       anna     girl
2       sydney   girl
3        max      boy                             etc.

Table B

id      title                                      description
1       A good girl                             Anna is a very good girl
2      Max doesnt work hard                    Boys are always like that only

etc...

see i will first search for a match for 'anna' in the table B's title and description and if a match is found in either of them then i'll store that id in table A only in the field 'matched id' I'll do the same procedure for 'girl' and then for 'sydney' and so on

Answer 1

SELECT  *
FROM    A
JOIN    B
ON      b.title IN (a.name, a.group)
        OR b.description IN (a.name, a.group)
WHERE   a.name = 'Anna'
        AND a.group = 'girl'

Since INDEX_UNION's are not very efficient in MySQL, it may be better to split the queries (especially if your tables are InnoDB):

SELECT  *
FROM    (
        SELECT  b.id
        FROM    A
        JOIN    B
        ON      b.title IN (a.name, a.group)
        WHERE   a.name = 'Anna'
                AND a.group = 'girl'
        UNION
        SELECT  b.id
        FROM    A
        JOIN    B
        ON      b.description IN (a.name, a.group)
        WHERE   a.name = 'Anna'
                AND a.group = 'girl'
        ) bo
JOIN    B
ON      b.id = bo.id

Answer 2

This query should do the trick:

UPDATE a SET matched_id =
 (SELECT b.id FROM b
  WHERE b.title LIKE CONCAT(' % ',a.name,' % ')
  OR b.description LIKE CONCAT('% ',a.name,' %')
  OR b.title LIKE CONCAT('%',a.group,'%')
  OR b.description LIKE CONCAT('%',a.group,'%')
  LIMIT 1)
WHERE EXISTS
 (SELECT a.id FROM b
  WHERE b.title LIKE CONCAT('% ',a.name,' %')
  OR b.description LIKE CONCAT('% ',a.name,' %')
  OR b.title LIKE CONCAT('%',a.group,'%')
  OR b.description LIKE CONCAT('%',a.group,'%')
  LIMIT 1)

Some remarks to this:

• LIMIT 1 was necessary, as each subquery can and will return more than 1 row
• Not 100% sure if the order you want/need is used, you may need some further testing for that and use order by if needed
• it may also be better to use an extra table for the groups (to reduce duplicate entries) and maybe one extra mapping table, so that you can map all results from B to A

EDIT: if names need to match perfectly (unlike girl/girls), you can just add a space in front of the name: '% ',a.name,' %'. If it gets more complicated I would suggest using regular expressions (REGEX). I modified the query with the spaces (for names only), so feel free to try it again.