CODEWITHSUNDEEP
sqlsql-serverstring-comparison
user2713440
user2713440

Reputation: 67

Compare names of one table with list of another table

I have a dataset of firm pairs and want to check whether one of the partner names or both partner names of the listed firm pairs are contained in a list of companies of another table.

If one or two partners are contained in the list of company names, the value 1 should be inserted in the "EarlyStage" column of the list of firm pairs and 0 otherwise.

The list of company names looks as follows

Partner

1View
1-Net
3T
A&L

The list of firm pairs looks as follows including the "EarlyStage" column with the newly inserted values 

Partner11  |  Partner22  |  EarlyStage

Unisys     |  Campu      |      0
A&L        |  Silva      |      1
Jet        |  3T         |      1
1-Net      |  1View      |      1

The value of "EarlyStage" should be 0 in the first row as both partners Unisys and Campu are not included in the list of company names. In the other rows "EarlyStage" should take the value of 1 because either partner11 (cp. row 2) or partner22 (cp. row 3) or both (cp. row 3) are included in the list of company names.

I would really appreciate any help to solve this problem.

Thank you very much in advance.

Upvotes: 0

Views: 140

Answers (2)

podiluska
podiluska

Reputation: 51494

update Pairs
set EarlyStage = 1 
from Pairs
    inner join Companies on Companies.Company in (Pairs.Partner11, Pairs.Partner22)

Upvotes: 1

Chris L
Chris L

Reputation: 2292

Try this:

INSERT INTO EarlyStage
SELECT
CASE WHEN Partner11 in (select name from Partner) 
THEN 1
WHEN Partner22 in (select name from Partner)
THEN 1
ELSE 0
END

Upvotes: 0

Related Questions