MySQL same id but different value in another column contains needle with match order

Question

table
+-----+-------+---------+
|  id |   pid |   note  |
+-----+-------+---------+
|  1  |    66 |  la qux |
|  2  |    66 |  la foo |
|  3  |    66 |  la bar |
|  4  |    66 |  el foo |
|  5  |    27 |   aaaaa |
|  6  |    27 |  barAAA |
|  7  |    27 |  AAfooA |
|  8  |    43 |     aaa |
|  9  |    43 |     qux |
| 10  |    11 |    hehe |
| 11  |    98 |     foo |
+-----+-------+---------+

needle is a needle in a haystack, or find substring in string

For each group with the same pid, select one whose note (a string) contains a match for the first substring needle following the needle order. So if any note in that group contains the first needle select that one and move on, else see if any contain second needle etc. If the group has no needle matches, dont select anything from that pid group.

Wanted output:

If these were the needles used with the table above:

$needle1 = 'foo'  
$needle2 = 'bar' 
$needle3 = 'qux' 


+-----+-------+---------+
|  id |   pid |   note  |
+-----+-------+---------+
|  2  |    66 |  la foo |
|  7  |    27 |  AAfooA |
|  9  |    43 |     qux |
| 11  |    98 |     foo |
+-----+-------+---------+

How would you go about achieving this?

What I have so far:

SQL / php

$needle1 = 'foo';
$needle2 = 'bar';
$needle3 = 'qux';

$sql = "
        SELECT id, pid, note
        FROM `table`

        WHERE INSTR(`note`, '{$needle1}') > 0
            OR INSTR(`note`, '{$needle2}') > 0
            OR INSTR(`note`, '{$needle3}') > 0

        GROUP BY pid
        LIMIT 10
        ";

But this only finds the first one in the group that matches any needle

Why is the record containing `el foo` omitted from your expected results? — Tim Biegeleisen, Sep 12 '16 at 01:22
@TimBiegeleisen Ideally I want it to get only the first one it finds, using `GROUP BY` does this for my current code — Nell, Sep 12 '16 at 01:31

score 0 · Answer 1 · answered Sep 12 '16 at 01:46

There are probably neater ways to do this but here is one way

    CREATE TABLE #temptable
    (  
    id int,  
    pid int, 
    note varchar(20)
    ); 

    INSERT INTO #temptable (id, pid, note) VALUES 
    ('1', '66', 'la qux'),('7', '27', 'AAfooA'),
    ('2', '66', 'la foo'),('8', '43', 'aaa'),
    ('3', '66', 'la bar'),('9', '43', 'qux'), 
    ('4', '66', 'el foo'),('10', '11', 'hehe'),
    ('5', '27', 'aaaaa'),('11', '98', 'foo'),
    ('6', '27', 'barAAA');


    CREATE TABLE #tempNeedle
    (  
    id int,  
    note varchar(20)
    ); 

    INSERT INTO #tempNeedle (id, note)
    VALUES ('1', 'foo'),
    ('2', 'bar'),
    ('3', 'qux');

    Select t5.* from #temptable t5
    INNER JOIN(
    Select t1.id, t1.pid, t1.note,ROW_NUMBER()Over(Partition by t1.PID
    Order by t3.id)  as row
    from #temptable t1
    CROSS APPLY (Select t2.id from #tempNeedle t2 
            Where t1.note LIKE concat('%',t2.note,'%')
            ) t3
            )t4 ON t5.id=t4.id AND t5.pid=t4.pid
    Where t4.row=1

This won't even run because MySQL does not have analytic functions. — Tim Biegeleisen, Sep 12 '16 at 01:50
Thanks @TimBiegeleisen, your absolutely right. MySQL [doesnt support](http://stackoverflow.com/questions/1895110/row-number-in-mysql) analytic functions, I had forgotten as most of my work is in MSSQL. I'll leave it here for reference but dont have a MySQL box to test a syntactically correct example — M O'Connell, Sep 12 '16 at 02:07

score 0 · Accepted Answer · answered Sep 12 '16 at 01:57

0

I think you can get away using LIKE along with a simple join:

SELECT t1.id,
       t1.pid,
       t1.note
FROM yourTable t1
INNER JOIN
(
    SELECT pid, MAX(id) AS id
    FROM yourTable
    WHERE note LIKE '%foo%'
    GROUP BY pid
) t2
    ON t1.pid = t2.pid AND
       t1.id  = t2.id

answered Sep 12 '16 at 01:57

Tim Biegeleisen

502,043
27
286
360

How does this work for multiple keywords in a certain order? So if none have `'%foo%'` it will will try to match `'%bar%'` etc – Nell Sep 12 '16 at 12:32
You could union together the query I gave you with a second one having a different keyword. – Tim Biegeleisen Sep 12 '16 at 12:40

MySQL same id but different value in another column contains needle with match order

2 Answers2