SQL Like condition

Question

I have 2 sql tables first table called processed

relatedorders (type in nvarchar)
132,112,144,155
116,113
11,44,15,16

and second table called orders and looks like

orderid (type int)
132
112
155
116

I want to select orderid from orders table where the orderid exists in relatedorders in processed table

I tried somthing like

SELECT * 
FROM orders 
WHERE EXISTS(SELECT relatedorders 
             from processed 
             where orders.orderid  like %relatedorders %)

but it doesn't seem to work as it should, what am I doing wrong?

You should be using a join table not storing the values as comma-separated text in a single column. Even if it worked, you'll have problems with comma-separated values since, for example, you'd have not match `1116` but should match `116,...`, `...,116,...`, and `...,116`. — tvanfosson, Jul 14 '13 at 23:02
@tvanfosson so no simple way to match other than a seperate table? and about performance will a seperate table perform faster? — user2490355, Jul 14 '13 at 23:09
I think it would be faster in all cases, but with appropriate indexes it would be *much* faster as you won't incur a table scan. — tvanfosson, Jul 14 '13 at 23:11

score 3 · Accepted Answer · answered Jul 14 '13 at 23:17

The proper way to model this is with a one-to-many relationship through a join table. Add another table, perhaps called ProcessedRelatedOrders containing the id of an order and the id of a related order, one row per relationship. You will likely want. Use both columns as the primary key. The index on the primary key should allow fast look up for a join with the processed order.

SQL Like condition

1 Answers1