user1080247
user1080247

Reputation: 1166

how to use table without join in mysql

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

Answers (1)

Kabulan0lak
Kabulan0lak

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

Related Questions