Get all rows not matching with another table

Question

I've a tricky question. I have a table with numbers:

37823782
37823782
37823782
38478934
90003922

And another table with prefixes:

378
3847
384
001

I want to find all numbers matching the longest prefix. I succeded with this code:

    $result = mysql_query("SELECT numbers FROM table1 GROUP BY numbers") or die ("Query error code 1"); 
while($row = mysql_fetch_array($result))
{

    $numbers =$row["numbers"];

    $result2 = mysql_query("SELECT * FROM table2 WHERE '".$numbers."' LIKE CONCAT(prefix, '%') ORDER BY CHAR_LENGTH(prefix) DESC LIMIT 1");
    while($row2 = mysql_fetch_array($result2))
    {

        // That's it

    }       

}

Now what i want to simply make the opposite thing. I want to find all numbers not matching any prefix. In short in the above example i made i should get "90003922". I thought to use NOT LIKE CONCAT (prefix, '%') but it's not working. Any idea?

What about this sequence of prefixes 378 **3847** 384 001 **5647**? Which is longest? — Hamlet Hakobyan, Jan 06 '13 at 12:34
look at this!
this will help you!
[exept table][1] [1]: http://stackoverflow.com/questions/13160913/table-a-except-table-b-relating-more-than-one-column — , Jan 06 '13 at 12:38
see this!it will help you!
[except][1] [1]: http://stackoverflow.com/questions/13160913/table-a-except-table-b-relating-more-than-one-column — , Jan 06 '13 at 12:40
The "WHERE GROUP BY" is a mistake i made when i ctrl+v ctrl+c the query. — user1274113, Jan 06 '13 at 13:32
i solved this problem with a totally different query. it's a long story. thank you anyway — user1274113, Jan 16 '13 at 13:59

score 1 · Answer 1 · answered Jan 06 '13 at 12:34

1

You can try this

SELECT * FROM table2 WHERE '".$numbers."' NOT LIKE 'prefix%'

answered Jan 06 '13 at 12:34

Suresh Kamrushi

15,627
13
75
90

score 1 · Answer 2 · answered Jan 06 '13 at 12:50

1

One-query solution will look like this. Try it

SELECT * FROM table1 LEFT JOIN table2 ON table1.numbers LIKE CONCAT(table2.prefix,'%') WHERE table2.prefix IS NULL

answered Jan 06 '13 at 12:50

Alexander Taver

474
3
4

Thank you for your answer but it works in a strange way. I can see even numbers matching prefixes. For example i can see: "349000000" even if in my table2.prefix there's "349". My table1.numbers is something like "whatever:3495541496@blabla" so maybe i should use **LIKE CONCAT(table2.prefix,':%@')** but it's all the same. – user1274113 Jan 06 '13 at 13:28
Please, update your post with some exact samples of your data in column `numbers`. I'll be able to fix my query – Alexander Taver Jan 06 '13 at 14:12

Get all rows not matching with another table

2 Answers2