Antonio Mailtraq Antonio Mailtraq - 3 months ago 13
MySQL Question

Select rows from a table that are not in another where string length

In db MySQL I have two tables xxx1 with 58 rows and xxx2 with 71 rows.

The two tables are similar because in the column

name
on xxx1 and xxx2 I can have e.g. :


VIETNAM 1/35 KHE SANH GUN POSITION BUNKER (


on xxx2 table

and


VIETNAM 1/35 KHE SANH GUN POSITION BUNKER (I) RESIN MODEL KIT


on xxx1 table

If try this query on xxx1 table the output is correct :

mysql> SELECT
*
FROM
`xxx1` r
WHERE
r.name LIKE '%VIETNAM 1/35 KHE SANH ARTILLERY FIREBASE R%';
+----+----------------------------------------------------------+
| id | name |
+----+----------------------------------------------------------+
| 30 | VIETNAM 1/35 KHE SANH ARTILLERY FIREBASE RESIN MODEL KIT |
+----+----------------------------------------------------------+
1 row in set


Now I need select rows from a table that are not in another and I have tried this query but the output is empty :

mysql> SELECT
l.*
FROM
xxx2 l
WHERE
NOT EXISTS (
SELECT
NULL
FROM
xxx1 r
WHERE
r.name LIKE '%VIETNAM 1/35 KHE SANH ARTILLERY FIREBASE R%'
);
Empty set


What's the problem ?

How to do resolve this ?

Can you help me ?

Thank you in advance.

Answer

Since r.name LIKE '%VIETNAM 1/35 KHE SANH ARTILLERY FIREBASE R%' will return a row based on the question, the not exists clause will be false for all records in xxx2, therefore an empty resultset is generated.

If you would like to know which record in xxx2 does not have corresponding record in xxx1, where xxx2 content is the start of the content in xxx1, then use left join with is null condition:

select xxx2.name
from xxx2
left join xxx1 on xxx2.name=left(xxx1.name,char_length(xxx2.name))
where xxx1.name is null