Possible on UNION ALL-CodePudding

I have a master table with USER and COMPANY called USER1, which maps a user to a company that the user can access and I have another master table that has a list of all available companies called COMPANY1. If a User is assigned '' company in USER1 table then it means that the user can access all companies in the COMPANY1 table.

USER1 table

User          |          Company
username1     |          MyCompany2
username2     |          MyCompany1
username3     |          
username4     |        



COMPANY1 table

Index       |      Company
1           |      MyCompany1
2           |      MyCompany2
3           |      MyCompany3
4           |      MyCompany4

Is there any way for me to do a UNION ALL to get the following:

NEW table

User          |          Company
username1     |          MyCompany2
username2     |          MyCompany1
username3     |          MyCompany1
username3     |          MyCompany2
username3     |          MyCompany3
username3     |          MyCompany4
username4     |          MyCompany1
username4     |          MyCompany2
username4     |          MyCompany3
username4     |          MyCompany4

I would really appreciate any input on this matter. :)

I tried something like `SELECT [User], [Company Name] FROM [USER1] WHERE [Company Name] <> ''

UNION ALL

SELECT [User],
(SELECT [Company] FROM [COMPANY1]) [Company Name]
FROM [USER1]
WHERE [Company Name] = ''`

Obviously this gave me an error that I can only have one result. :( How do I loop through one table like the COMPANY1 table based on the result of another table like USER1.

Thanks.

CodePudding user response：

You can use CROSS JOIN of the Company1 table to the users with the user's assigned company of '' and this will produce one row for each user and each company pair, and will allow for your UNION ALL, like so:

SELECT [User], [Company Name] 
FROM [USER1] 
WHERE [Company Name] <> ''
UNION ALL
SELECT u.[User], c.[Company] as [Company Name]
FROM [USER1] as u
CROSS JOIN [Company1] as c
WHERE u.[Company Name] = ''

Documentation with examples for CROSS JOIN : sql-cross-join-with-examples