CODEWITHSUNDEEP
sql
Nahiann
Nahiann

Reputation: 9

Sql table query for tables with no direct link

I have Table_A that has a column: 'FullName',columnB, .. and other columns

I have Table_B that has columns: 'FirstName', 'LastName','Job_OID'

Evidently, If we join the characters of FirstName and LastName, it matches Full_Name.

This is the only thing common between these two tables!

Problem: When the Full Name is "John Smith" in Table_A, I want to fetch his Job_OID from table_B.

In simple language, select job_oid from table_B where ('FirstName' + 'LastName') = Table_A.FullName;

Upvotes: 0

Views: 73

Answers (3)

Shushil Bohara
Shushil Bohara

Reputation: 5656

You can also write query in this way:

SELECT B.job_oid 
FROM Table_A  A
INNER JOIN table_B  B ON A.FullName = CONCAT(B.FirstName, ' ', B.LastName)

Upvotes: 1

Dmitriy
Dmitriy

Reputation: 11

try this:

SELECT job_oid 
FROM table_B 
JOIN Table_A ON Table_A.FullName = Table_B.FirstName+' '+TableB.LastName

Upvotes: 1

Joe Taras
Joe Taras

Reputation: 15379

I think, you must normalize your DB, but if it isn't possible, you can write following query:

SELECT B.*
FROM Table_B B
WHERE EXISTS
    (SELECT 'PERSON'
    FROM Table_A A
    WHERE B.FirstName + ' ' + B.LastName = A.FullName)

You can use, otherwise, JOIN command like this:

SELECT B.*
FROM Table_B B
JOIN Table_A A
    ON B.FirstName + ' ' + B.LastName = A.FullName

So, in this way you link two tables with a concatenation of two fields (FirstName and LastName of Table_B) with FullName stored in Table_A

Upvotes: 0

Related Questions