0

I've got a requirement to add an additional item of data to an existing row and insert the result in a second table. The data item is different for each row I am selecting, so I can't just add it to the SELECT statement. The original query is:

SELECT player_id,token_id,email FROM players
WHERE token_id in (101,102) OR email in ("test4@test.com");

I'd like to be able to do something like a Row Constructor and write the query something like this:

SELECT player_id,token_id, email, key_val FROM players
WHERE (token_id, key_val) in ( (101, 'xyz'),(102,'abc'))
   OR (email, key_val) in ( ("test4@test.com", 'qpr') );

So that the second value ('key_val') from the pair in the IN clause would be added into the SELECT output as the last column. And then the whole lot will get inserted into the final table.

The number of items in the IN clause will vary from 3 to potentially 100's.

Really sorry if this is a dup. I've looked up things like: Select Query by Pair of fields using an in clause MySQL: How to bulk SELECT rows with multiple pairs in WHERE clause I guess I could use a temporary table but I'm concerned about the number of times that this is going to be called.

Edit--

To clarify, the source table is something like:

player_id, token_id, email
===================================
1          101       null
2          102       null
3          null      test4@test.com

and the date being supplied is:

(token_id=101, key_val='xyz'),(token_id=102, key_val='abc'),(email='test4@test.com', key_val='qpr')

and the intended output would be:

player_id  token_id  email          keyy_val
========== ========= ============== ========
1          101       null           zyz
2          102       null           abc
3          null      test4@test.com qpr

Hope this makes it clearer.

Community
  • 1
  • 1
jmc
  • 813
  • 10
  • 18

1 Answers1

1

try this

SELECT player_id,token_id, email, key_val 
FROM players
WHERE token_id in (101,102) AND key_val IN ('xyz','abc')
OR ( email in ("test4@test.com") AND  key_val IN ('qpr') );

EDIT -. try this

 SELECT player_id,token_id, email, key_val 
 FROM ( select player_id,token_id, email, 
  if(`token_id` =101 , 'xyz',
  if(`token_id` =102 , 'abc' ,
  if(email = "test4@test.com" , 'qpr' , NULL))
  ) key_val

from players 
)p

DEMO SQLFIDDLE

echo_Me
  • 37,078
  • 5
  • 58
  • 78
  • This isn't quite the same because it will also match rows with token_id/key_val pairs of 101,abc and 102/xyz, which is not in the OP's requirements. – hrunting Feb 09 '13 at 16:56
  • Perhaps I didn't explain well enough. 'key_val' is not in the DB. It's a piece of data related to token_id (coming from a client device) which I want to add as a row in the SELECT output. – jmc Feb 09 '13 at 16:56