using a join to find data in a second table? I'm very new to SQL and don't really understand JOINS as much as I would like, or even if this would be a good case for a join.

I have two tables:

Table1

Headings:
"folder","OwnerNumber"

Data
"folderx","1,2"
"foldery","1,3"


and

table2:

Headings:
"record","emailaddress","samId"

data
"1","user1@domainX.com","user1"
"2","Billy@domainy.com","william"
"3","Admin@domainz.com","Tom"


My application has starts off with the folder name, and the application user's SAM ID. In this case, the app user is 'Tom' and he wants to know about 'folderx'.

I need a query that I can pass in "folderx" and "Tom" into and get the email addresses that belong to the users 1, 2 and Tom.

The same query, if asked for "foldery" and user "Mike" should give me email addresses for users 1 &amp; 3 (with no match for the SAMID of what was passed in).

Now, I know I can do this with multiple calls from my app.  One to get the numbers:

get ownernumber from table1 where folder = 'folderx'


then taking that value, and making the query in my app, 

select emailsaddresses from table2 where record='1' or record='2' or samID = 'Tom'


But I figured there had to be a better way, and a way that didn't involve making two calls from my application to SQL to get the data that was all within SQL to begin with.

I suspect this is a JOIN, but what I am not clear on is:


The response from the first query gives me a single string "1,2" and am using my application to split that, and reformat it to a proper select statements for my second query.  How would I break that up within a SQL query?
if I were able to simply do some kind of @Query2 = split('result from query 1'), how would I then use that result set to make the query that goes against table 2?  Items may be listed as "1,2,3" or as "1, 2, 3" in the source, so possible spaces will need to be taken into account.


All I need when I am done is the email addresses that own a specific folder, and the email address of any matching SAMID (if one exists).

Rules:


Folder "ownernumbers" should never be blank
SAMID may or may not have a matching entry.
There may be duplicate email addresses in table2 (but not usually)
Record number is assigned by SQL so it should always be unique in Table2.


My database is the latest version of SQL Express downloaded from Microsoft.  My app is written in Powershell but for the sake of this question, I doubt that it matters.

If desired, I can add the powershell code, but this really isn't a powershell specific question.

Finally, I won't be offended if you treat me like a newbie here.  When it comes to SQL, I am self taught (bad practices as well as good) and sometimes need an answer spoon fed so I understand it.  Please do not assume I know what I am doing here.  If you make assumptions, please detail them so I can understanding them fully.

This is a very small dataset.  Maybe 1000 records in all.  Query does not have to be under efficient and a more understandable solution is preferred to a terse but more efficient one.
In SQL Server 2016 or better, you can use STRING_SPLIT. I didn't dig in to all of your rules but here is a general approach. Note that the DECLARE @Tables are just there to mock up data. You'd remove those and change @Table1 and @Table2 in the final query to test against your real tables.

DECLARE @Table1 TABLE(folder varchar(32), OwnerNumber varchar(32));
INSERT @Table1 VALUES('folderx','1,2'),('foldery','1,3');

DECLARE @Table2 TABLE(record int, emailaddress varchar(320), samId varchar(32));
INSERT @Table2 VALUES(1,'user1@domainX.com','user1'),
  (2,'Billy@domainy.com','william'),(3,'Admin@domainz.com','Tom');

-- input params
DECLARE @folder varchar(32) = 'folderx', 
        @samID  varchar(32) = 'Tom';

SELECT * FROM @Table1 AS t1
OUTER APPLY STRING_SPLIT(t1.OwnerNumber, ',') AS sp
FULL OUTER JOIN @Table2 AS t2
ON sp.value = t2.record
WHERE t1.folder = @folder
   OR t2.samID = @samID;


Now, of course, with this brittle design, you'll need to protect yourself from values in OwnerNumber like 1,foo,2 but hopefully that's something the home grown application somehow already protects you from.