SQL query for CONTAIN check and JOIN excel TABLE

Question

Supplier

Requestor

Expected RESULTS

SQL Code :

SELECT T2."Fullname",T2."Town", T2."Age", T1."Surname"
FROM "Requestor" as T2
LEFT JOIN "Supplier" as T1
ON (T2."Fullname" LIKE '%' + T1."Name" + '%')
ORDER BY T2."Fullname" ;

Not sure why the value of Surname is not getting populated or passed into Expected Table ?

INNER JOIN , WILD , EXIST SQL condition

Answer 1

You didn't clarify what it is that you wanted to know, so I would assume that you want to know how to fix your JOIN query.

Since LEFT JOIN shows all rows from T2, it is only logical that T1 has no rows that match this rather peculiar JOIN condition. Ideally, you should join two tables with Foreign Keys - like how each row with a nickname in T2 would need to have a Foreign Key column containing IDs from the other table that you want to JOIN it on. So try to fix the structure of the tables and get more accustommed to how JOINs work.

For instance, you should have a JOIN statement like:

SELECT * FROM requestor as T2
JOIN supplier AS T1
ON id = T1.id 

In the ON id = T1.id line, the first id mentions T2's ID. It would be even better if you would have an intermediary table to help join the two tables together which would contain the history of operations between the two entities - several of them for each one. Like in this structure of a decent schema. You can only imagine how many VIEWs one can create for this table.

I would encourage you to learn how JOINs work in SQL in-depth. They are fairly common in the real world and most of the difficult work in regards to SQL revolves around JOINs, so once you've got enough experience with them to know how and when to use them, it will makey your life way easier. You should also have a crystal clear understanding of the structure of your tables to know how to join them.

Answer 2

Use || to join two strings into one

SELECT T2."Fullname",T2."Town", T2."Age", T1."Surname"
FROM "Requestor" as T2
LEFT JOIN "Supplier" as T1 on T2."Fullname" LIKE '%' || T1."Name" || '%'
ORDER BY T2."Fullname" ;

Demo here