SQL query sometimes returns wrong results

Question

I have this query:

query = "SELECT DISTINCT set_number " +
        "FROM next_workout_exercises " +
        "WHERE next_id = " + workoutid + " AND exercise_number = " + exercise_number;

and it returns some results.

After this, I have another query as follows:

query = "SELECT * FROM (SELECT * FROM next_workout_exercises INNER JOIN exercises WHERE next_workout_exercises.exercise_id = exercises.id )" +
" WHERE next_id = " + workoutid + " AND exercise_number = " + exercise_number + " AND set_number = " + set_number;

And this returns zero results SOMETIMES. The set_number is the same from the first query and no data has changed.

Can anyone comment why this might be happening?

The tables in question:

CREATE TABLE exercises
(
    id             INTEGER PRIMARY KEY,
    exercise_name  TEXT,
    explanation    TEXT,
    type           INTEGER,
    target_body    INTEGER,
    exercise_video TEXT,
    exercise_pic1  TEXT,
    exercise_pic2  TEXT,
    picturetype    INTEGER,
    backedup       INTEGER
);    

CREATE TABLE next_workout_exercises
(
    id              INTEGER PRIMARY KEY,
    next_id         INTEGER,
    exercise_id     INTEGER,
    weightkg        REAL,
    weightlb        REAL,
    reps            INTEGER,
    reps2           INTEGER,
    set_number      INTEGER,
    exercise_number INTEGER,
    incrementkg     REAL,
    incrementlb     REAL,
    resttime1       INTEGER,
    resttime2       INTEGER,
    resttime3       INTEGER,
    failures        INTEGER,
    failuresallowed INTEGER,
    percentage      REAL,
    reptype         INTEGER,
    exercisetype    INTEGER,
    backedup        INTEGER,
    FOREIGN KEY(next_id) REFERENCES nextWorkout(id),
    FOREIGN KEY(exercise_id) REFERENCES exercises(id)
);

Can you edit your question to include the table schemas and provide a minimal set of data in the tables that shows the wrong behavior you're experiencing? — CDahn, Jul 02 '17 at 22:16
@Mizan if you could format the tables in a code block, it would be helpful for future readers to parse your question. Also, please give us a little bit of sample data so we can see how the data may be affecting your query. — CDahn, Jul 02 '17 at 22:30
Editing all thanks to Felix Pamittan. I can't provide the data because on my own data and machine this works fine. I have an android app, which gives an exception and I can see that the first query returns some results but the second one returns nothing which shouldn't be happening. — Mizan, Jul 02 '17 at 22:52
Can an exercise/next exercise be in two different sets with the same exercise number? — Sal, Jul 02 '17 at 23:01
please post data and expected result... you don't have the same queries in the OP — RoMEoMusTDiE, Jul 02 '17 at 23:16
[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection — marc_s, Jul 02 '17 at 23:54
@Mizan why did you provide the first SQL query? It doesn't look like you're using any values from it in the second query. Does it serve some purpose for the second query? Could you clarify and provide some sample data that's causing this to fail? If you can't provide sample data, your feedback will be as questionable as the question itself. — CDahn, Jul 03 '17 at 01:13

Jeff Holt · Answer 1 · 2017-07-02T23:47:53.363

0

If (a) nobody's modifying the data, (b) your second query is using the same values for workoutid and exercise_number, and (c) your second query is using the set_number returned from the first query, then it must be true that your incorrectly specified join condition is why you're only sometimes getting the data you desire.

You specify a join condition like this:

ON next_workout_exercises.exercise_id = exercises.id

You don't use a WHERE clause to do the join unless you write your join using more traditional means.

A more concise way to write the second query is this:

SELECT n.*, e.*
FROM next_workout_exercises n
INNER JOIN exercises e ON n.exercise_id = e.id
WHERE n.next_id = :workoutid
AND n.exercise_number = :exercise_number
AND n.set_number = :set_number

Oh, and please do your DBA a favor by using placeholders. This looks like a ideal piece of code to use them.

edited Jul 02 '17 at 23:47

answered Jul 02 '17 at 23:42

Jeff Holt

2,940
3
22
29

The `where` clause is semantically equivalent to the `on` statement in that it's limiting the output. It may not be as efficient, depending on DBMS. At worst, his `INNER JOIN` is producing the full cartesian product of both tables, and the WHERE is dropping everything that he doesn't want. Either way, the `on` you've provided _should_ provide him the same output as his `where`. – CDahn Jul 03 '17 at 01:16
Thanks a lot. This just might be the issue. I'll test it out and get back to you. Thanks for the help! – Mizan Jul 03 '17 at 19:22
@Mizan take a look at these other excellent SO questions to see the relevance of `ON` versus `WHERE`: [1](https://stackoverflow.com/questions/1613304/ansi-joins-versus-where-clause-joins), [2](https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause), and [3](https://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause). – CDahn Jul 04 '17 at 00:46

score 0 · Answer 2 · answered Jul 03 '17 at 01:22

0

It would seem that your data set doesn't contain a row in next_workout_exercises that contains a next_id and exercise_number for the set_number you're using in the second query.

Your first query only says "Give me all of the unique set_number values from the table," but that doesn't guarantee that the set_number you're specifying in the second query is from the unique set from the first query.

answered Jul 03 '17 at 01:22

CDahn

1,795
12
23

No I have checked and the second query uses those set numbers which are present in the first. – Mizan Jul 03 '17 at 11:12
@Mizan sample data or it didn't happen. – CDahn Jul 03 '17 at 18:11

SQL query sometimes returns wrong results

2 Answers2