Brecht27 Brecht27 - 1 month ago 5
MySQL Question

Query not showing the right results

I have build a query like this:

SELECT
c.*,
(SELECT COUNT(cursus_id) FROM cursusdienst_bestellingen where cursus_id = c.id AND datum_afhaling IS NULL AND datum_verwijderd IS NULL AND studentid = '$studentid') as besteld,
(SELECT COUNT(cursus_id) FROM cursusdienst_bestellingen where cursus_id = c.id AND datum_afhaling IS NOT NULL AND datum_verwijderd IS NULL AND datum_afgehaald IS NULL AND studentid = '$studentid') as afhalen,
(SELECT datum_afhaling + INTERVAL 14 DAY FROM cursusdienst_bestellingen where cursus_id = c.id AND datum_afhaling IS NOT NULL AND datum_verwijderd IS NULL AND datum_afgehaald IS NULL AND studentid = '$studentid') as datum_afhaling,
(SELECT COUNT(cursus_id) FROM cursusdienst_bestellingen where cursus_id = c.id AND datum_afgehaald IS NOT NULL AND datum_verwijderd IS NULL AND cb.studentid = '$studentid') as afgehaald
FROM
cursusdienst c
LEFT JOIN
cursusdienst_bestellingen cb
ON
cb.cursus_id = c.id
WHERE
c.studierichting = '1ste Bachelor'
ORDER BY
c.artikel asc, cb.studentid


This is a registration/ordering system for students and courses. There are a lot of student using this system. The student must be registered before they can order a course.
I have a problem with my query here because i see courses of every student in the same list. An example here:

Checkbox Course Price Status
Checkbox Course 1 12.00 Ordered
Checkbox Course 1 12.00 Ordered
Checkbox Course 2 25.50 Ready to deliver
Checkbox Course 3 15.00


As you see here, the query shows me all the course info of 2 students (it is just an example) they are logged in and ordered courses.
The first student ordered Course 1 en 2 and the second student ordered only Course 1.
If i logged in as a student i want to see only my status with the course i have ordered but i see the rest of the courses as well (without any status like course 3) so i can order other courses in the same form.
What is not want to see is what others are ordered!

So i want to see only this if i, as a student, ordered Course 1 and 2. Course 3 is also in the list because i can order it now but i didn't ordered before (so it has no status):

Checkbox Course Price Status
Checkbox Course 1 12.00 Ordered
Checkbox Course 2 25.50 Ready to deliver
Checkbox Course 3 15.00


If i add "AND cb.studentid = '$studentid'" to the WHERE i see only my ordered courses and not the rest of the courses i didn't ordered.

Edit: (thanks to SunKnight0) set the "AND cb.studentid = '$studentid'" in the ON part and not in the WHERE part! So the working code is shown below:

SELECT
c.*,
(SELECT COUNT(cursus_id) FROM cursusdienst_bestellingen where cursus_id = c.id AND datum_afhaling IS NULL AND datum_verwijderd IS NULL AND studentid = '$studentid') as besteld,
(SELECT COUNT(cursus_id) FROM cursusdienst_bestellingen where cursus_id = c.id AND datum_afhaling IS NOT NULL AND datum_verwijderd IS NULL AND datum_afgehaald IS NULL AND studentid = '$studentid') as afhalen,
(SELECT datum_afhaling + INTERVAL 14 DAY FROM cursusdienst_bestellingen where cursus_id = c.id AND datum_afhaling IS NOT NULL AND datum_verwijderd IS NULL AND datum_afgehaald IS NULL AND studentid = '$studentid') as datum_afhaling,
(SELECT COUNT(cursus_id) FROM cursusdienst_bestellingen where cursus_id = c.id AND datum_afgehaald IS NOT NULL AND datum_verwijderd IS NULL AND cb.studentid = '$studentid') as afgehaald
FROM
cursusdienst c
LEFT JOIN
cursusdienst_bestellingen cb
ON
cb.cursus_id = c.id AND cb.student_id = '$studentid'
WHERE
c.studierichting = '1ste Bachelor'
ORDER BY
c.artikel asc, cb.studentid

Answer
SELECT 
   c.*,
   (SELECT COUNT(cursus_id) FROM cursusdienst_bestellingen where cursus_id = c.id AND datum_afhaling IS  NULL AND datum_verwijderd IS NULL AND studentid = '$studentid') as besteld,
   (SELECT COUNT(cursus_id) FROM cursusdienst_bestellingen where cursus_id = c.id AND datum_afhaling IS NOT NULL AND datum_verwijderd IS NULL AND datum_afgehaald IS NULL AND studentid = '$studentid') as afhalen,
   (SELECT datum_afhaling + INTERVAL 14 DAY FROM cursusdienst_bestellingen where cursus_id = c.id AND datum_afhaling IS NOT NULL AND datum_verwijderd IS NULL AND datum_afgehaald IS NULL AND studentid = '$studentid') as datum_afhaling,
   (SELECT COUNT(cursus_id) FROM cursusdienst_bestellingen where cursus_id = c.id AND datum_afgehaald IS NOT NULL AND datum_verwijderd IS NULL AND cb.studentid = '$studentid') as afgehaald
FROM 
   cursusdienst c
LEFT JOIN
   cursusdienst_bestellingen cb
ON
   cb.cursus_id = c.id AND cb.studentid = '$studentid'
WHERE 
   c.studierichting = '1ste Bachelor'
ORDER BY 
   c.artikel asc, cb.studentid
Comments