Reputation: 1166
i have multiple tables related with each other but i have one table not related to anyone of this tables and want to use this table within (where clause). So how do I do this?
/* this is the table i want to use without join (d_checkupnext_alert.chx_days)
* notice : chx_days return on value with one row only
*/
SELECT
d_checkupinfo.*,
d_branch.*,
d_checkup.*,
d_patient.*,
d_checkupnext_alert.chx_days
FROM d_checkupinfo
LEFT JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
LEFT JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
LEFT JOIN d_checkup ON d_checkupinfo.chi_type_id = d_checkup.checkup_id
WHERE CURDATE() BETWEEN DATE_SUB(FROM_UNIXTIME(chi_nextdate), INTERVAL chx_days day) AND FROM_UNIXTIME(chi_nextdate)
/* update */ i use this code and it works but give me errors
SET @days = (select chx_days from d_checkupnext_alert LIMIT 1);
SELECT d_checkupinfo.*, d_branch.*, d_checkup.*,
d_patient.*
FROM d_checkupinfo
LEFT JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
LEFT JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
LEFT JOIN d_checkup ON d_checkupinfo.chi_type_id = d_checkup.checkup_id
WHERE CURDATE() BETWEEN DATE_SUB(FROM_UNIXTIME(chi_nextdate), INTERVAL @days day) AND FROM_UNIXTIME(chi_nextdate)
Upvotes: 0
Views: 53
Reputation: 2136
Best way is to use a simple JOIN
or a ',
' like this :
SELECT
d_checkupinfo.*,
d_branch.*,
d_checkup.*,
d_patient.*,
d_checkupnext_alert.chx_days
FROM d_checkupinfo
LEFT JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
LEFT JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
LEFT JOIN d_checkup ON d_checkupinfo.chi_type_id = d_checkup.checkup_id
, d_checkupnext_alert
WHERE CURDATE() BETWEEN DATE_SUB(FROM_UNIXTIME(chi_nextdate), INTERVAL chx_days day) AND FROM_UNIXTIME(chi_nextdate)
But if you don't want, you could use System Variables : see "MySQL System Variables"
Upvotes: 1