CODEWITHSUNDEEP
sqlpostgresqljoin
Sandy
Sandy

Reputation: 466

Join two tables, not joined with any foreign keys and different unrelated ids

I have two tables.

CompanyNames:
company_name text (Unique)
companyname_id bigint 

HomeNickNames:
home_nickname text (Unique)
nickname_id bigint

So, the rows will be like:

    CompanyNames
    ==============
    company_name   ||   companyname_id
|:--------------------------------------:|
     Robert         |      123
     Mr.Robert      |      123
     Mr.RobertGrant |      123



    HomeNiceNames
    ==============
    home_nickname  ||   nickname_id
|:-------------------------------------:|
     Robert         |      431
     Robb           |      431
     DearRobb       |      431

Assume almost every company name and nickname can be joined because of one same company name and nickname.

I need a query that can generate the following result:

       name_id || home_nickname
|:-----------------------------------:|
       123     |   Robb
       123     |   Robert
       123     |   DearRobb

Upvotes: 0

Views: 51

Answers (2)

Alex
Alex

Reputation: 17289

Still not sure what are you looking for. But just my guess:

http://sqlfiddle.com/#!9/a2f0a/2

SELECT t.companyname_id, t2.home_nickname
FROM (SELECT companyname_id, GROUP_CONCAT(company_name) names
FROM CompanyNames
GROUP BY companyname_id) t
LEFT JOIN HomeNiceNames
ON FIND_IN_SET(HomeNiceNames.home_nickname, t.names)
LEFT JOIN HomeNiceNames t2
ON HomeNiceNames.nickname_id = t2.nickname_id

Upvotes: 1

dnoeth
dnoeth

Reputation: 60472

Find Robertfirst and then all nicks with that id:

select 
   c.companyname_id
  ,n2.home_nickname
from CompanyNames as c
join HomeNiceNames as n
  on c.company_name = n.home_nickname -- match Robert
join HomeNiceNames as n2
  on n.nickname_id = n2.nickname_id   -- match nickname with same id as Robert

Upvotes: 2

Related Questions