SQL Is this Outer or Inner Join and how to join on Array

Question

Seemed simple when I started and have done this before, now I confused myself and at a road block.

Have two tables: News_Table and a People_Table. Under the News_Table there is a field: News_People_Contributed and it has the ID's of the People_Table in array format (1,4,7,10) thus Four People contributed. I am creating a search parameter that looks up News_Header AND News_People_Contributed and can't figure how to create the search column.

News_Table
News_ID
News_Header
News_People_Contributed

People_Table
People_ID
People_First_Name...

Is it something like...

Select*
From News_Table
Left Join News_Table
On People_Table.People_ID IN (News_Table.News_People_Contributed)
Where Search_Param Like '%News_Header%' OR Search_Param Like '%People_First_Name%'

The problem is (News_Table.News_People_Contributed) is a string and the ID's are not. Plus I may not have people contributed etc. To make the issue even more complex, I'm doing this in MS Access instead of MySql, so have to code it "old school" sql for work around.

News_People_Contributed has IDs as a string, not multi-value field? Don't see how you could have ever done this query before if the field is a string. Storing as string is very bad design. — June7, Mar 23 '17 at 19:25
it was a multi-select menu to make it easy. Otherwise I have to create a third table linking the the two other tables - done that before two. I like using arrays because it is cleaner and easier to do for loops on other items. Can always split the string too to call out array. — Brewy, Mar 23 '17 at 19:32
This is poor table structure. You should have a third table joining the two. Then create a query on the three tables and search that. You mention mysql, What does that has to do with anything? — AVG, Mar 23 '17 at 20:25
Well, there is a third table with multi-value field - you just don't see it or work directly with it because it is a hidden system table. Review https://support.office.com/en-us/article/Using-multivalued-fields-in-queries-6F64F92D-659F-411C-9503-B6624E1E323A — June7, Mar 23 '17 at 20:26
I mentioned MySql because because Access doesn't support some of the SQL language that MySql does and with Access you write the queries slightly different. Most people on this forum respond with true Sql as applied to MySql databases. If you know older access you know they are very similar but definitely not the same. I know the 3rd table is the way to go, but that also means I have to run a multiple insert, where as the array was easier. Thanks for the comments. — Brewy, Mar 23 '17 at 20:58
http://stackoverflow.com/questions/22327708/access-db-table-split-field-sql-command — nicomp, Mar 24 '17 at 13:32

score 0 · Answer 1 · answered Mar 25 '17 at 02:46

Perform a cross join and filter on matches in the string list. It says nothing about efficiency or form (as already commented on), but it works.

SELECT *
FROM News_Table, People_Table
WHERE InStr([News_People_Contributed],CStr([People_ID])) > 0;

This only answers part of the problem: The join -- the issue everyone seemed concerned about in the initial comments. There are not enough details about about the Search_Parameter to provide help on that. Supply more detail if you need more help there.

SQL Is this Outer or Inner Join and how to join on Array

1 Answers1