CODEWITHSUNDEEP
sqlteradata
Aleksei Nikolaevich
Aleksei Nikolaevich

Reputation: 325

How to indirectly join two tables

I have two tables

perid  firstname lastname 
1       mike      prokosini
2       nick      doug
3       paul      jor
4       riki      thui
5       mun       tyui

and second table

caseno   name 
13       mike-prokosini
32       nickdoug
33       paul l.jor
34       riki  thui
35       mun kl. tyui

I must join these tables such that if firstname is a substring of name AND lastname is a substring of name THEN the rows should be connected.

I know this is not correct, but I cannot even imagine how we can possibly do this 

  SELECT * 
    FROM table1 , table2 
    WHERE firstname LIKE '%name%' AND lastname LIKE '%name%'

Please help me

Upvotes: 0

Views: 598

Answers (2)

Mohsen Karimi
Mohsen Karimi

Reputation: 1

there are some conditions :

1 - if you have an indirect relationship you should use one or more relational tables in your database that have a direct relation but it is not recommended it is demanding and time-consuming for your SQL server, like this:

select * 
from table_name1 as a  
inner join table_name2 as b 
on a.col1 = b.col1
inner join relational_table as c 
on b.col1 = c.col1

Be careful that sometimes a.col1, b.col1, and c.col1 are not the same so you may get the wrong answer

2 - if you have a direct relation between your table well it is clear you should just join them based on their keys

select *
from table_name1 as a 
inner join table_name2 as b 
on a.col1 = b.col1

3 - if there is not any relationship you should use a comma in front of the 'from' command or use 'cross join', but it will show you cartesian products, like this:

select * 
from table_name1, table_name2 
-----or 
select * 
from table_name1 cross join table_name2

enjoy it

Upvotes: 0

user330315
user330315

Reputation:

You need to concatenate the actual column values with the wildcard:

SELECT * 
FROM table1 t1
  JOIN table2 t2 ON t2.name LIKE '%'||t1.firstname||'%' 
                AND t2.name LIKE '%'||t1.lastname||'%'

Upvotes: 1

Related Questions