I wish to Select all rows which contain any element from an array

Question

I am trying to do a search engine, something like stackoverflow here. I need to select all rows that contain any of selected tags.

Table

RowID    Question    Tags
  1        'who'     'C#'
  2       'what'     'SQL'
  3       'where'    'C#,PHP'
  4       'when'     'PHP,SQL'

string[] myTags=new string{"c#","PHP'};

then my sql select statement something like this

Select * from table where Tags like myTags[]

I wish to have results like

RowID    Question    Tags
  1        'who'     'C#'
  3       'where'    'C#,PHP'
  4       'when'     'PHP,SQL'

Of course I know this is syntactically incorrect that is why I am here.

Do you mean something like `SELECT * FROM myTable WHERE myField IN (1,2,3,4)`? — MatBailie, May 09 '12 at 18:02
Tried that it doesn't work for me, I will modify my question slightly to show why.... I hope — General Grey, May 09 '12 at 18:05
***Don't*** store multiple tags in a string. It's the complete antithesis of what a relational database should be. Have a separate table with one tag per row `(1,'C#'),(2,'SQL'),(3,'C#'),(3,'PHP'),(4,'PHP'),(4,'SQL')`. Of any SQL anti-patterns, this is the first one to learn and avoid. *[One field in one row = one item of data. Not several.]* — MatBailie, May 09 '12 at 18:08

Saeed Amiri · Accepted Answer · 2012-05-09T18:24:50.537

2

Linq version:

myTable.Where(x=>myTags.Contains(x.Tag));

Sql Version:

Select * from table where Tag in {'c#','SQL','PHP'}

If one item can have too many tags, you should change a little your database design (adding relational table), it's not a good way to have a string which contains too many tags.

Update: For your current DB sample you can do this with linq:

myTable.Where(x=>myTags.Any(y=>x.Tags.Contains(y)));

edited May 09 '12 at 18:24

answered May 09 '12 at 18:01

Saeed Amiri

22,252
5
45
83

I tried this before asking the question the problem is it has to match the tag exactly. where as I am going for a like with wild cards. The reason I need this is because I am storing multiple tags as a string "C#,SQL,PHP' – General Grey May 09 '12 at 18:04
@K'Leg, See my update, IMO you should add another table to distinguishing different tags in one item. In fact no one converts list of subitems to string, it's inefficient and also it's not Normal DB design. – Saeed Amiri May 09 '12 at 18:05
There would likely only be 2 tags max per entry, and usually only 1. Hrmm I don't like the idea of an entire new table for such a trivial matter – General Grey May 09 '12 at 18:11
1

Your data model needs the new table. Using a like '%tag%' will be very slow as it triggers a table scan for every query. – Filip De Vos May 09 '12 at 18:12
linq is great but for now I am trying to go sql only for the query part. though that looks good. – General Grey May 09 '12 at 18:12
1

@K'Leg - Then use an XML text file instead of a relational database? – MatBailie May 09 '12 at 18:13
The database is already established, but yes I can change it. There are so few tags involved here I might even add each one as a column with a bit value. How does that sound? – General Grey May 09 '12 at 18:14
2

@K'Leg - The advantage of a normalised table is that you can then get a huge performance increase by indexing the `tag` field. This allows index seeks, rather than scanning the whole table. With a single string field you always get a whole table scan. With multiple fields you need multiple indexes and complex (or multiple) queries. In terms of design, performance, maintainability, flexibility, and even simplicity, the normalised data version is nearly always the way to go. *(By which I mean, you need an extreme case to have any chance of it not being the right way.)* – MatBailie May 09 '12 at 18:19
@K'Leg, You can simply convert this linq to sql, anyway simply changes are coming, and sooner or later you will see you need some changes, also tricky ways like what I mentioned doesn't work in general. (there are too many bugs with this ways and I never offer to use them) – Saeed Amiri May 09 '12 at 18:19
ok one last attempt before I give in. The query I had offered was far simplier than reality. In reality it would be more like Select * from table where name=@Name and Date=@date and Question like '%'+@Question+'%' and Tags in ('C#','PHP') do you still suggest I use a seperate table for tags, as there would always be a full table scan, I believe – General Grey May 09 '12 at 18:36
@K'Leg, Sure I'll suggest this. I had a query (update) which was around 500 lines of sql commands (join, case, groupby, special selects, ...), because of this I should set my database as the way easing just this query? No, just design your DB in correct way. Finally I should note that you can write your query in linq and watch in debug mode to find related sql command (these is applicable for simple querys like this one). – Saeed Amiri May 09 '12 at 18:44

score 0 · Answer 2 · answered May 09 '12 at 18:01

0

Are you against making multiple SQL calls? If not you could do a for loop to run a query for each item in your array of tags?

answered May 09 '12 at 18:01

WhoaItsAFactorial

3,538
4
28
45

1

This is very rarely an optimal choice. – MatBailie May 09 '12 at 18:03

I wish to Select all rows which contain any element from an array

2 Answers2

Linked