SQL | Selecteer profielen, ZONDER calender item binnen datetimes
ik ben m'n views aan het uitbreiden en versimpelen voor de gebruikers.
Hiervoor heb ik een zoekformulier met bepaalde waardes, functie, regio, rijbewijs, certificaten.
Al eerder met wat hulp ben ik hierop gekomen:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT p.prof_userid,p.prof_firstname,p.prof_id, COUNT(zr.zreg_regionid) as regions FROM saf_profiles p
JOIN dns_users u ON p.prof_userid = u.user_id
JOIN saf_zzpregions zr ON zr.zreg_userid = u.user_id
JOIN saf_zzpcertification c ON c.zcert_userid = u.user_id
JOIN saf_functions f ON f.func_userid = u.user_id
WHERE f.func_typeid LIKE "' . $function . '" AND zr.zreg_regionid LIKE "' . $region . '" AND p.prof_driverlicense LIKE "%' . $license . '%" AND c.zcert_certid LIKE "' . $certificate . '" AND u.user_level = 1 AND p.prof_visible = 1
GROUP BY p.prof_id,u.user_id
JOIN dns_users u ON p.prof_userid = u.user_id
JOIN saf_zzpregions zr ON zr.zreg_userid = u.user_id
JOIN saf_zzpcertification c ON c.zcert_userid = u.user_id
JOIN saf_functions f ON f.func_userid = u.user_id
WHERE f.func_typeid LIKE "' . $function . '" AND zr.zreg_regionid LIKE "' . $region . '" AND p.prof_driverlicense LIKE "%' . $license . '%" AND c.zcert_certid LIKE "' . $certificate . '" AND u.user_level = 1 AND p.prof_visible = 1
GROUP BY p.prof_id,u.user_id
Dit werkt wel naar behoren, maar nu probeer ik deze uit te breiden met 2 datetimes.
De bedoeling is dat de opgegeven datetimes NIET voor mogen komen in de calendar.
Hiermee doe ik wel een controle of de ingevoerde boeking conflicteerd. (niet of deze NIET conflicteerd, beetje krom eigenlijk, want ben alleen geintresseert of er conflicten zijn)
Code (php)
1
2
3
4
5
2
3
4
5
SELECT cal_id FROM saf_calendar WHERE cal_userid = ' . $userid . ' AND cal_accepted = 1 AND (
(cal_datefrom BETWEEN ("' . $from . '") AND ("' . $till . '"))
OR
(cal_datetill BETWEEN ("' . $from . '") AND ("' . $till . '"))
)
(cal_datefrom BETWEEN ("' . $from . '") AND ("' . $till . '"))
OR
(cal_datetill BETWEEN ("' . $from . '") AND ("' . $till . '"))
)
Ik zie alleen niet zo goed... hoe ik deze in mijn zoekquery (de eerste) kan combineren, om mij alleen resultaten te geven van profielen welke nog NIET zijn geboekt tussen de ingevoerde datetimes.
Gewijzigd op 15/07/2016 21:52:08 door Dennis WhoCares
Edit:
Zou je jouw bericht willen bewerken en een topictitel invullen die je vraag- of probleemstelling omschrijft?
Alvast bedankt!
Alvast bedankt!
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
SELECT p.prof_userid,p.prof_firstname,p.prof_id, COUNT(zr.zreg_regionid) as regions FROM saf_profiles p
JOIN dns_users u ON p.prof_userid = u.user_id
JOIN saf_zzpregions zr ON zr.zreg_userid = u.user_id
JOIN saf_zzpcertification c ON c.zcert_userid = u.user_id
JOIN saf_functions f ON f.func_userid = u.user_id
LEFT JOIN saf_calendar sc ON sc.cal_user_id = u.user_id AND cal_accepted = 1 AND (
cal_datefrom BETWEEN "' . $from . '" AND "' . $till . '"
) OR cal_datetill BETWEEN "' . $from . '" AND "' . $till . '"
WHERE f.func_typeid LIKE "' . $function . '" AND zr.zreg_regionid LIKE "' . $region . '" AND p.prof_driverlicense LIKE "%' . $license . '%" AND c.zcert_certid LIKE "' . $certificate . '" AND u.user_level = 1 AND p.prof_visible = 1 AND sc.cal_id IS NULL
GROUP BY p.prof_id,p.prof_firstname,p.prof_userid
JOIN dns_users u ON p.prof_userid = u.user_id
JOIN saf_zzpregions zr ON zr.zreg_userid = u.user_id
JOIN saf_zzpcertification c ON c.zcert_userid = u.user_id
JOIN saf_functions f ON f.func_userid = u.user_id
LEFT JOIN saf_calendar sc ON sc.cal_user_id = u.user_id AND cal_accepted = 1 AND (
cal_datefrom BETWEEN "' . $from . '" AND "' . $till . '"
) OR cal_datetill BETWEEN "' . $from . '" AND "' . $till . '"
WHERE f.func_typeid LIKE "' . $function . '" AND zr.zreg_regionid LIKE "' . $region . '" AND p.prof_driverlicense LIKE "%' . $license . '%" AND c.zcert_certid LIKE "' . $certificate . '" AND u.user_level = 1 AND p.prof_visible = 1 AND sc.cal_id IS NULL
GROUP BY p.prof_id,p.prof_firstname,p.prof_userid
Ben van Velzen op 15/07/2016 22:44:32:
Een left join icm IS NULL zou uitkomst kunnen bieden, uiteraard niet getest maar zoiets?
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
SELECT p.prof_userid,p.prof_firstname,p.prof_id, COUNT(zr.zreg_regionid) as regions FROM saf_profiles p
JOIN dns_users u ON p.prof_userid = u.user_id
JOIN saf_zzpregions zr ON zr.zreg_userid = u.user_id
JOIN saf_zzpcertification c ON c.zcert_userid = u.user_id
JOIN saf_functions f ON f.func_userid = u.user_id
LEFT JOIN saf_calendar sc ON sc.cal_user_id = u.user_id AND cal_accepted = 1 AND (
cal_datefrom BETWEEN "' . $from . '" AND "' . $till . '"
) OR cal_datetill BETWEEN "' . $from . '" AND "' . $till . '"
WHERE f.func_typeid LIKE "' . $function . '" AND zr.zreg_regionid LIKE "' . $region . '" AND p.prof_driverlicense LIKE "%' . $license . '%" AND c.zcert_certid LIKE "' . $certificate . '" AND u.user_level = 1 AND p.prof_visible = 1 AND sc.cal_id IS NULL
GROUP BY p.prof_id,p.prof_firstname,p.prof_userid
JOIN dns_users u ON p.prof_userid = u.user_id
JOIN saf_zzpregions zr ON zr.zreg_userid = u.user_id
JOIN saf_zzpcertification c ON c.zcert_userid = u.user_id
JOIN saf_functions f ON f.func_userid = u.user_id
LEFT JOIN saf_calendar sc ON sc.cal_user_id = u.user_id AND cal_accepted = 1 AND (
cal_datefrom BETWEEN "' . $from . '" AND "' . $till . '"
) OR cal_datetill BETWEEN "' . $from . '" AND "' . $till . '"
WHERE f.func_typeid LIKE "' . $function . '" AND zr.zreg_regionid LIKE "' . $region . '" AND p.prof_driverlicense LIKE "%' . $license . '%" AND c.zcert_certid LIKE "' . $certificate . '" AND u.user_level = 1 AND p.prof_visible = 1 AND sc.cal_id IS NULL
GROUP BY p.prof_id,p.prof_firstname,p.prof_userid
Hi Ben,
sorry voor de late reactie, het lijkt inderdaad goed te werken! Bedankt, ik wist helemaal niet van IS NULL af :o
Enorm bedankt!