Search code examples
sqlsqlitejoin

JOIN two SELECT queries, leaving ALL element from first table even without corrispondence

I need help in joining two select in a Sqlite database.

I would like to list all elements of the first table even if there is no corrispondence in the second table... I write down the tables beacuse I don't know how to explain well in words... 😅

These are my tables:

tab1

enter image description here

tab2

enter image description here

expected result

enter image description here

My query is:

SELECT tab1.ID, tab1.email, tab1.email2, tab2.name
FROM tab1
LEFT JOIN tab2
WHERE tab2.email LIKE tab1.email
   OR tab2.email LIKE tab1.email2

...and what I get is: NOTHING...

Note: I made all tables in markdown but I always get this error

Your post appears to contain code that is not properly formatted as code. Please indent all code by 4 spaces using the code toolbar button or the CTRL+K keyboard shortcut. For more editing help, click the [?] toolbar icon.

Very frustrating doing this post! 😫

Solution

  • How about instr function?

    select a.id, a.email, a.email2, b.name
from tab1 a left join tab2 b on instr(b.email, a.email)  > 0
                             or instr(b.email, a.email2) > 0
order by a.id;

    Result is

    ID  EMAIL                   EMAIL2                  NAME
--  --------------------    ---------------------   ------
1   pippo@example.com       pippo2@example.com      Pippo
2   pluto@example.com       null                    Pluto
3   paperino@example.com    mrpaperino@example.com  null
4   franco@example.com      null                    null
5   null                    mario@rossi.com         Mario

    Have a look at fiddle.