SQL: Splitting Results

Question

I have a requirement to take the output from the following and use it in an 'IN' Statement in a second query

SELECT
MPFlist
FROM My_Table_01
WHERE Reference = <Variable>

Where the Value of MPFlist is returned as (as an example) NB this list could return a variable number of MPF codes

'LCADL','LCECC','LCHBK','LCHIN'

One of my problems is that I have Read Only Access to the MS SQL DB in question.

What I need to do is somehow split this output so it can be used in another query

In other Words I want this

SELECT 
Product,
Company,
Circuit
From My_Table_02  
WHERE MPFID IN
(
SELECT
MPFlist
FROM My_Table_01
WHERE Reference = <Variable>
)

to run as this

SELECT 
Product,
Company,
Circuit
From My_Table_02  
WHERE MPFID IN
(
'LCADL','LCECC','LCHBK','LCHIN'
)

TIA for any suggestions

I don't really understand what you are asking. Those two queries should run the same. Are these tables on different databases or something? I don't see what having read-only access would have to do with it either — Kevin, Oct 06 '17 at 12:43
Also, you have this tagged as mysql, but talk about ms sql in the question. Which is it? — Kevin, Oct 06 '17 at 13:04
"One of my problems is that I have Read Only Access to the MS SQL DB in question.". How is that a problem when running a select query? As far as I can see `SELECT Product, Company, Circuit From My_Table_02 WHERE MPFID IN ( SELECT MPFlist FROM My_Table_01 WHERE Reference = )` should do what you want already? Or you could make it use an inner join instead, probably a bit more efficient. — ADyson, Oct 06 '17 at 13:12

score 0 · Answer 1 · edited Oct 06 '17 at 13:11

0

Could you try to use Inner Join

SELECT 
  Product, 
  Company, 
  Circuit 
FROM 
  My_Table_02 T2 
  INNER JOIN My_Table_01 T1
    ON T2.MPFID = T1.MPFID

edited Oct 06 '17 at 13:11

ADyson

57,178
14
51
63

answered Oct 06 '17 at 13:06

Sameer Ghorpade

1
1

score 0 · Answer 2 · edited Oct 07 '17 at 04:18

It is a MS SQL DB I am working against.

To clarify

If I run this query

SELECT 
Product,
Company,
Circuit
From My_Table_02  
WHERE MPFID IN
(
SELECT
MPFlist
FROM My_Table_01
WHERE Reference = <Variable>
)

I get No results.

This is because I get one value returned (which is the expected result) from this query

SELECT
MPFlist
FROM My_Table_01
WHERE Reference = <Variable>

That single value is (as an example)

'LCADL','LCECC','LCHBK','LCHIN'

Rather than (as in this example) 4 values such as

LCADL
LCECC
LCHBK
LCHIN

Which would normally be processed in sub query automatically.

This is also why I can't use the Inner Join as in one table each field is a single code (eg LCADL) whereas in the second table the field value is (for example) 'LCADL','LCECC','LCHBK','LCHIN'

The relevance of Read Only access is that the solutions I have seen so far hinge on being able to Create temporary Tables, which I can't do.

technically its not a change to my question, it's a solution I've come too. IMHO there's a subtle difference but thanks for the sage advice :) — Mike, Oct 06 '17 at 21:56

score 0 · Answer 3 · answered Oct 06 '17 at 20:45

Ok I think I've found something that does the trick.

Adapted from https://stackoverflow.com/a/19837110/8732261

DECLARE @id_list VARCHAR(MAX) = 
(
SELECT
REPLACE(MPFlist,'''','')
FROM My_Table_01
WHERE Reference = <Variable>
)
DECLARE @table TABLE ( id VARCHAR(50) )
DECLARE @x INT = 0
DECLARE @firstcomma INT = 0
DECLARE @nextcomma INT = 0

SET @x = LEN(@id_list) - LEN(REPLACE(@id_list, ',', '')) + 1 -- number of ids in id_list

WHILE @x > 0
    BEGIN
        SET @nextcomma = CASE WHEN CHARINDEX(',', @id_list, @firstcomma + 1) = 0
                              THEN LEN(@id_list) + 1
                              ELSE CHARINDEX(',', @id_list, @firstcomma + 1)
                         END
            INSERT  INTO @table
            VALUES  ( SUBSTRING(@id_list, @firstcomma + 1, (@nextcomma - @firstcomma) - 1) )
            SET @firstcomma = CHARINDEX(',', @id_list, @firstcomma + 1)
            SET @x = @x - 1
        END

SELECT 
Product,
Company,
Circuit
From My_Table_02  
WHERE MPFID IN
(
SELECT *
FROM @Table
)

SQL: Splitting Results

3 Answers3