mercredi 31 octobre 2018

If value in both table then assign 1

    Table1  CompanyID  Location  #-of-employees
          5234       NY          10
          5268       DC          2
          5879       NY          8
          6897       KS          100
          8789       CA          1
          9992       OH          201
          9877       TX          15

Table2 CompanyID   #-of-Shareholders
          5234            5
          5879            2
          6897            4
          8789            2

I have two table with the column CompanyID. In table2 you can find companies that have shareholders and in table1 you can find all the companies. So in table 1 I want to add a dummy variable that assign a 1 if the companyID is in table2(which means the company has shareholders) and a 0 if not.

Expected output:
    Table1  CompanyID  Location  #-of-employees Dummy
              5234       NY          10           1
              5268       DC          2            0
              5879       NY          8            1
              6897       KS          100          1
              8789       CA          1            1
              9992       OH          201          0
              9877       TX          15           0

I tried using this query but it doesn't give me the output I expect.

SELECT CASE WHEN companyID IN table2 THEN 1
ELSE 0
END AS dummy
FROM table1

Aucun commentaire:

Enregistrer un commentaire