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