CODEWITHSUNDEEP
mysqlsql
Benjamin Schurtenberger
Benjamin Schurtenberger

Reputation: 11

Error in SQL-Syntax..Showing just 3 instead of 4 rows

I got a SQL-Query what should return 4 rows.. But it just returns 3 rows..

My Tables are looking like this: https://i.sstatic.net/10bog.png https://i.sstatic.net/8BuvD.png

Here's my Syntax: 

SELECT gruppe
FROM gruppen
WHERE gruppe LIKE '%Vormittag%'
AND gruppe_id NOT IN (SELECT gruppe_id
                      FROM daten
                      GROUP BY gruppe_id
                      HAVING COUNT(gruppe_id) >= 15)
ORDER BY gruppe ASC;

I should get a list of 4 Groups..But i just get this 3:

"Gruppe 2 | Vormittag"

"Gruppe 3 | Vormittag"

"Gruppe 4 | Vormittag"

Where's the Problem that hides the first one?

Upvotes: 0

Views: 59

Answers (2)

Ruslan Veselov
Ruslan Veselov

Reputation: 337

You should add WHERE clause to your inner select:

WHERE gruppe_id IS NOT NULL

You can check fiddle here.

Additional information about this behavior you can find here.

Upvotes: 2

Joël Salamin
Joël Salamin

Reputation: 3576

Try this query:

SELECT G.gruppe
FROM gruppen G
WHERE G.gruppe LIKE '%Vormittag%'
   AND G.gruppe_id NOT IN (SELECT D.gruppe_id
                      FROM daten D
                      WHERE D.gruppe_id IS NOT NULL
                      GROUP BY D.gruppe_id
                      HAVING COUNT(D.gruppe_id) >= 15)
ORDER BY G.gruppe ASC

I just added aliases for the tables in order to avoid conflict between columns with the same name.

Instead of that, your query seems to be correct!

Upvotes: -1

Related Questions