4

First Statement : 

Select GroupMember
FROM Group

Result: Los Angeles,San Diego (it is a string)

Second Statement : 

 SELECT *
 FROM Store_Information 
 WHERE Store_Name IN ('Los Angeles', 'San Diego');

How to turn Los Angeles,San Diego(one data) to IN ('Los Angeles', 'San Diego')?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Molly
  • 53
  • 6

4 Answers4

3

Use inner query

SELECT * FROM Store_Information
WHERE Store_Name IN (Select GroupMember FROM Group)
Bohemian
  • 412,405
  • 93
  • 575
  • 722
Dhaval
  • 2,341
  • 1
  • 13
  • 16
1

you can write a query as

SELECT * 
FROM 
Store_Information WHERE Store_Name IN (
SELECT Split.a.value('.', 'VARCHAR(100)') AS String   
       FROM  (SELECT  CAST ('<M>' + REPLACE(groupname, ',', '</M><M>') + '</M>' 
                             AS XML) AS groupname       
               FROM  Group) 
       AS A 
CROSS APPLY groupname.nodes ('/M') AS Split(a))
Deepshikha
  • 9,896
  • 2
  • 21
  • 21
0

Although you can do it with a subquery, a better way is with a join:

SELECT s.*
FROM Store_Information s
JOIN Group g ON g.GroupMember = s.Store_Name
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Why is a join better? Duplicate rows in the result if `Store_Name` is not unique perhaps? – Mikael Eriksson Dec 15 '14 at 07:18
  • is it ok to join with name field ??i know it' possible but i don't think it is feasible .. – Dhaval Dec 15 '14 at 07:19
  • @MikaelEriksson there will be duplicate rows if `GroupNember` is not unique - in this case add `DISTINCT` keyword after `SELECT`. Regarding performance, many modern databases will convert the `IN` version to the `JOIN` version, but some may not. The join is preferred because it allows merge join access plan. – Bohemian Dec 15 '14 at 07:25
  • 1
    @Dhaval it is OK - joining by non-key fields is one of the strengths of RDBMS. If an index is defined on `Group(GroupMember)` it will perform well. – Bohemian Dec 15 '14 at 07:26
  • Ah, yes of course `GroupNember`. I got the tables mixed up. The part about the merge join not being used for `IN` is new to me. Looks like I need to do some testing on that one. – Mikael Eriksson Dec 15 '14 at 07:28
  • 1
    Ok, now I have checked and in SQL Server you **will** get a merge join using `IN` if the optimizer thinks is appropriate. The rewrite with `DISTINCT` when using a join can give you the same plan as with `IN` but only if you fetch a column with a unique constraint/primary key. If you don't, SQL Server will have to do the job of removing duplicate rows. – Mikael Eriksson Dec 15 '14 at 07:41
0

Look like the 

Select GroupMember
FROM Group

only return 1 line which is Los Angeles,San Diego

So you can use this

SELECT * FROM Store_Information
WHERE Store_Name IN (Select ''' + REPLACE(GroupMember,',',''',''') + ''' FROM Group)
  1. append ' in front of Los
  2. replace , -> ','
  3. append ' at the end of Diego
ah_hau
  • 768
  • 4
  • 11