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,438
  • 68
  • 55
  • 126
Molly
  • 53
  • 6

4 Answers4

3

Use inner query

SELECT * FROM Store_Information
WHERE Store_Name IN (Select GroupMember FROM Group)
Bohemian
  • 389,931
  • 88
  • 552
  • 692
Dhaval
  • 2,291
  • 1
  • 11
  • 13
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,358
  • 2
  • 18
  • 20
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
  • 389,931
  • 88
  • 552
  • 692
  • 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