Left Join ON LIKE needs something like LIMIT

Question

( EDIT : final LIMIT will not solve. Please read carefully.)

I have a table that scans text in another LEFT JOIN and LIKE table, and I would like to limit the number of results per left row .

• The usergames AS u table contains substrings of user-entered strings representing game names.
• The games AS g table contains a basic list of game names.

• When the user enters the name of the game, for example, “Miami Hotline”, the input is divided, and 2 entries are created in substrs : one contains "hotline" and one containing "miami" (in the piece column) and both contain the full name of the game (in the name column )

• If the full name of the game does not match anything in the main list (for example, the user mistakenly enters into "Haatline Miami"), I check the main list for suggestions, such as any game with "Haatline" or "miami" .

This is my request and it works great:

 SELECT u.name,g.name AS suggestion,count(g.name) FROM usergames u LEFT JOIN games g ON CONCAT(' ', g.name, ' ') LIKE CONCAT('% ', u.piece, ' %') GROUP BY g.name, u.name ORDER BY u.name ASC, count(g.name) DESC 

The problem is that sometimes there are too many offers. There may be 1,000 games that have the word "Miami" in it. How to limit the number of lines created by the left-side join? See the data below, some results are from the hotline, and some are from miami. How can I limit the results of X to a substring.

SAMPLE DATA

substrs

games

result

_{(Note: trailing LIMIT is not what I am looking for, because I do not want to limit the total number of results, but only the result for each element that arises from LIKE join)}

_{(Note: 2 → w / spaces is there to ensure that only complete word matches are returned. Mentally delete them if they are confused, the question will be the same if it was g.name LIKE s.piece ) sub>}

Answer 1

So, here's what you need: 2 subqueries and 2 cross joins with a counting table. I STILL do not know how to limit the number of results of a subquery, but this solution makes it work much faster, so the need for reduction is significantly reduced. ( limit is still not applicable.)

At first I found out about CROSS JOINs with table tables, thanks to my brother. With this, I was able to come up with this crazy solution. If someone knows the best way after considering my decision, please write:

 SELECT us.name, gs.idGame, suggestion, count(suggestion) FROM ( SELECT u.idGame, u.name, SUBSTRING_INDEX(SUBSTRING_INDEX(u.name, ' ', nn), ' ', -1) AS user_substr FROM usergames u CROSS JOIN ( SELECT N FROM _tally ) n WHERE u.idGame IS NULL AND nn <= 1 + (LENGTH(u.name) - LENGTH(REPLACE(u.name, ' ', ''))) HAVING LENGTH(user_substr) > 1 ) us, ( SELECT idGame, g.name suggestion, SUBSTRING_INDEX(SUBSTRING_INDEX(g.name, ' ', nn), ' ', -1) AS game_substr FROM games g CROSS JOIN ( SELECT N FROM _tally ) n WHERE nn <= 1 + (LENGTH(g.name) - LENGTH(REPLACE(g.name, ' ', ''))) HAVING LENGTH(game_substr) > 1 ) gs WHERE user_substr NOT IN ( SELECT piece from _piecesrestricted ) AND game_substr NOT IN ( SELECT piece from _piecesrestricted ) AND ( LENGTH(user_substr)>3 OR user_substr = suggestion ) AND user_substr = game_substr GROUP BY suggestion, us.name ORDER BY us.name ASC, count(suggestion) DESC 

Answer 2

If you check the documentation here , you will see the following sentence:

 LIMIT <number of records> 

If I do not understand your question, this is the solution.