How to Save results from query in field for WHERE IN statement

[C Sharper]

I would like to query a table, and save the results into 1 field of a results table as below:

Table1:
StudentID FirstName LastName
JOHND John Doe
JANED Jane Doe
ROJERD Rojer Doe
JOHNNYJ Johnny Johnson

Table2:
SearchId Results
1 JOHND, JANED, ROJERD

Table 2 populated by 2 queries
SELECT StudentID FROM Table1 WHERE LastName = 'Doe'
i build the comma separated list from that query and then insert into table2
insert into Table2 (Results) VALUES (@StudentList)


Then I would like to select from the student table where student in results field like

SELECT * FROM Table1 WHERE StudentID IN (SELECT Results FROM Table2 WHERE SearchID = @SearchID)

I've tried adding single quotes to each side of each result, but that does not work either...

[ck9663]

Your entire requirement can be sum up into a single query:

 
SELECT * FROM Table1 WHERE LastName = 'Doe'
 
 

If you could give us more detail, like why do you need to put it into a separate table only to be used as searched argument when in fact it'll give the same result as the above query?

We might be able to suggest more efficient solution.

Happy Coding!!!


~~ CK

[C Sharper]

I need it for saved search results. There are many more fields a person will be able to search by as well... I just did this as a simple example of what I am looking for. I am saving the search results because the items in the tables change so frequently.

Basically I just want a person to be able to fill out their search fields, perform the search, and save the results in a table -- which I can use later to display the results...

[ck9663]

If that's the case, it might be better to do an inner join.

Good Luck!!!

~~ CK

[C Sharper]

I don't see how an inner join will help with my situation. I want to save a string (like an array of results) to one field in the database. Then I want to be able to use that string for my WHERE IN statement. I need to know how to format that string when I save it so I can use it in my SELECT WHERE IN statement... I have to save the results in the table so users can see their search results 6 months from now

[ck9663]

Well you have 2 options...

 
SELECT * FROM Table1 t1
where exists (select 1 from FROM Table2 t2 WHERE t2.SearchID = @SearchID and charindex(t1.studentid, t2.results) > 1
 
 

OR..

 
SELECT t1.* 
FROM Table1 t1
inner join t2.SearchID = @SearchID and charindex(t1.studentid, t2.results) > 1
 
 

I just use whatever table you have already. So I assume you have the result as delimited string. As I said, INNER JOIN will do.

Happy Coding!!!

~~ CK

[C Sharper]

@ ck9663 - I used your first option, and it looks to be working as I needed... Thanks!