1

I have a table namely tbl_plays. It has the following columns and data

id(PK)|    gameid    |    drawid    |   etc
----------------------------------------------
   1  |     1509     |      73      |   etc

I am running this simple query.

SELECT id FROM tbl_play WHERE id = 1 OR gameid = 0

This query is run as a subquery which returns the id column data to a variable.

Now my question, why do I get the error SUBQUERY RETURNS MORE THAN ONE ROW where as it returns only one row. In Workbench it returns an editable result-set, and myAdmin shows edit/delete buttons.

Does it have anything related to the fact that I am fetching a PK column data? If it does, then how can I over come this.

Regards

P.S: id column has all unique values.

  • 3
    What if one row matches the id, and another one matches the gameid? – Jeroen Jan 22 '14 at 20:42
  • All the values or row ID and GameID are unique. All values are greater than zero. Sorry for not mentioning that. – echo_salik Jan 23 '14 at 6:48
  • Why are you looking for the GameID then? You could do with just searching for the ID. – Jeroen Jan 23 '14 at 17:02
0

You need to add the LIMIT clause

SELECT id FROM tbl_play WHERE id = 1 OR gameid = 0 LIMIT 1;

This is an oversimplified answer.

If there is a row with PK=1, you find it.

If not, a full table scan may ensue because of gameid not being indexed.

You are better off splitting the query with a UNION. Perhaps, like this:

SELECT id FROM
(
    SELECT id FROM tbl_play WHERE id = 1
    UNION
    (SELECT id FROM tbl_play WHERE gameid = 0 LIMIT 1)
) A ORDER BY id LIMIT 1;

Since I do not know the data, I cannot predict any performance results.

Give it a Try !!!

UPDATE 2014-01-23 11:01 EST

The reason it works because of the goal I set for the query : Retrieve one value.

First look at the UNION

    SELECT id FROM tbl_play WHERE id = 1
    UNION
    (SELECT id FROM tbl_play WHERE gameid = 0 LIMIT 1)

The first SELECT can return at most one row.

The second SELECT can return at most one row because of the LIMIT 1.

In the worst case scenario, the UNION will have two rows.

Now look at the whole query:

SELECT id FROM
(
    SELECT id FROM tbl_play WHERE id = 1
    UNION
    (SELECT id FROM tbl_play WHERE gameid = 0 LIMIT 1)
) A ORDER BY id LIMIT 1;

The outer part of the query will receive either 0, 1, or 2 rows.

Then, the part ORDER BY id LIMIT 1 forces the outer query to choose the first value.

  • It works. :) Can you tell me the reason why this works? – echo_salik Jan 23 '14 at 6:59
  • I updated my answer to explain why my suggested query works. – RolandoMySQLDBA Jan 23 '14 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.