0

I have a table in MySQL as following:

I was trying the following queries:

select * from test.score_card where playerId in(2,3);

select * from test.score_card where playerId not in(0);

The table: score_card

I need a single line query so that it should show the output as if I pass

playerId within WHERE IN clause then it should show the selected rows OR if I

don't pass any value then it should select all rows.

Shambhu
  • 121
  • 3
  • 11
  • How are you calling this? Is this a raw SQL query or is it based on user input and passed to the database afterwards? If it's based on user input and nothing is passed in, you can use a "id in coalesce(input-string, id)" and pass in "2,3,4,x" and it will say "If there is a value in input-string, use it, otherwise match where every id in the table matches itself." One more thing - "0" does not mean "doesn't exist". NULL means "there is no value". In your table above, there is no "0" and you could just as easily substitute "and id is not null", which is meaningless as ID is in your PK. – T Gray Jun 20 '17 at 23:13

1 Answers1

0

so an or STATEMENT?

select * from test.score_card 
where (playerId in(2,3)) or (playerId not in(0));

Or the more complex which would only return the second set if the count equals 0 on the first set:

SELECT * FROM test.score_card
WHERE playerId in (2,3)
UNION
SELECT * FROM test.score_card
WHERE (
SELECT count(*) FROM test.score_card
WHERE playerId in (2,3))=0 AND playerId not in (0);

(Untested, might have a typo)

Norbert
  • 6,026
  • 3
  • 17
  • 40
  • Here it is selecting all rows because of `not in(0)` . But if I pass the `playerId` then it should select with matched `id`. If I don't pass the `playerId` then it should select all rows. – Shambhu Jun 20 '17 at 19:47
  • Not passing the playerId (eg no value) is not an option with SQL. You would have to go to a stored procedure instead which has IF statements for validation). Or look at my edited version and add a dummy value in your in when you do not have data, then that version could work nicely for your case – Norbert Jun 20 '17 at 19:49
  • Well, I was searching for the single line query, if it's not possible, then I'll go for procedure. Can u help for that? thanks. – Shambhu Jun 20 '17 at 19:53
  • https://stackoverflow.com/questions/7566948/mysql-procedure-with-if-statement should get you started. It contains all the logic you are looking for (not too much :) ) – Norbert Jun 20 '17 at 19:56