Reputation: 15
I have 8 tables in my database and I need to have all necessary data in one query from them.
When not using a where
statement, my query is working, but when I use LIKE
and OR LIKE
in my query, the where
statement is not correctly working
My original query is:
SELECT `mhr_patients`.`id`,
`mhr_patients`.`fullName`,
`mhr_patients`.`nationalCode`,
`mhr_patients`.`age`,
`mhr_patients`.`firstGCS`,
`mhr_patients`.`fileNumber`,
`mhr_patients`.`isUnKnown`,
`mhr_patients`.`docDetail`,
`mhr_patients`.`presentation`,
`mhr_patients`.`appRegisterTime`,
`mhr_patients`.`inspectorRegisterTime`,
`mhr_patients`.`patientStatusDetail`,
`mhr_patients`.`patientDetail`,
`mhr_patients`.`status`,
`docT`.`text` AS docText,
`tolOp`.`name` AS tolOpName,
`tolOp`.`color` AS tolOpColor,
`tolOp`.`res1` AS tolOpTextColor,
`pLog`.`breathing`,
`pLog`.`bodyMovement`,
`pLog`.`faceMovement`,
`pLog`.`gag`,
`pLog`.`cough`,
`pLog`.`cornea`,
`pLog`.`pupil`,
`pLog`.`dollEye`,
`pLog`.`secondGCS`,
`pLog`.`sedation`,
`pLog`.`inspector`,
`pLog`.`status` As pLogStatus,
`pLog`.`section`,
`pLog`.`id` AS pLogId,
`pLog`.`typeOfSection`,
`pLog`.`lastUpdateTime`,
`pLog`.`isTransfer`,
`pLog`.`opu`,
`hos`.`name` AS hosName,
`mhr_opu`.`name` AS opuName,
`mhr_opu`.`id` AS opuId,
`mhr_states`.`name` AS cityName,
`mhr_inspectors`.`name` AS insName
FROM (`mhr_patients`)
JOIN `mhr_doc` AS docT ON `docT`.`id` = `mhr_patients`.`doc`
JOIN `mhr_tol_options` AS tolOp ON `tolOp`.`id` = `mhr_patients`.`patientStatus`
JOIN `mhr_patients_log` AS pLog ON `pLog`.`pId` = `mhr_patients`.`id` AND pLog.id = (SELECT MAX(mhr_patients_log.id) FROM mhr_patients_log WHERE mhr_patients_log.pId = mhr_patients.id)
JOIN `mhr_hospitals` AS hos ON `hos`.`id` = `pLog`.`hospital`
JOIN `mhr_opu` ON `mhr_opu`.`id` = `pLog`.`opu`
JOIN `mhr_states` ON `mhr_states`.`id` = `pLog`.`city`
JOIN `mhr_inspectors` ON `mhr_inspectors`.`id` = `pLog`.`inspector`
WHERE
`mhr_patients`.`status` = 5
GROUP BY `pLog`.`pId`
ORDER BY `mhr_patients`.`inspectorRegisterTime` asc
LIMIT 30
When I use the query above, the result is correct and when I change mhr_patients
.status
, a different result is returned,
but when I use the following query mhr_patients
.status
is not working and gives one result no matter what parameter
SELECT `mhr_patients`.`id`,
`mhr_patients`.`fullName`,
`mhr_patients`.`nationalCode`,
`mhr_patients`.`age`,
`mhr_patients`.`firstGCS`,
`mhr_patients`.`fileNumber`,
`mhr_patients`.`isUnKnown`,
`mhr_patients`.`docDetail`,
`mhr_patients`.`presentation`,
`mhr_patients`.`appRegisterTime`,
`mhr_patients`.`inspectorRegisterTime`,
`mhr_patients`.`patientStatusDetail`,
`mhr_patients`.`patientDetail`,
`mhr_patients`.`status`,
`docT`.`text` AS docText,
`tolOp`.`name` AS tolOpName,
`tolOp`.`color` AS tolOpColor,
`tolOp`.`res1` AS tolOpTextColor,
`pLog`.`breathing`,
`pLog`.`bodyMovement`,
`pLog`.`faceMovement`,
`pLog`.`gag`,
`pLog`.`cough`,
`pLog`.`cornea`,
`pLog`.`pupil`,
`pLog`.`dollEye`,
`pLog`.`secondGCS`,
`pLog`.`sedation`,
`pLog`.`inspector`,
`pLog`.`status` As pLogStatus,
`pLog`.`section`,
`pLog`.`id` AS pLogId,
`pLog`.`typeOfSection`,
`pLog`.`lastUpdateTime`,
`pLog`.`isTransfer`,
`pLog`.`opu`,
`hos`.`name` AS hosName,
`mhr_opu`.`name` AS opuName,
`mhr_opu`.`id` AS opuId,
`mhr_states`.`name` AS cityName,
`mhr_inspectors`.`name` AS insName
FROM (`mhr_patients`)
JOIN `mhr_doc` AS docT ON `docT`.`id` = `mhr_patients`.`doc`
JOIN `mhr_tol_options` AS tolOp ON `tolOp`.`id` = `mhr_patients`.`patientStatus`
JOIN `mhr_patients_log` AS pLog ON `pLog`.`pId` = `mhr_patients`.`id` AND pLog.id = (SELECT MAX(mhr_patients_log.id) FROM mhr_patients_log WHERE mhr_patients_log.pId = mhr_patients.id)
JOIN `mhr_hospitals` AS hos ON `hos`.`id` = `pLog`.`hospital`
JOIN `mhr_opu` ON `mhr_opu`.`id` = `pLog`.`opu`
JOIN `mhr_states` ON `mhr_states`.`id` = `pLog`.`city`
JOIN `mhr_inspectors` ON `mhr_inspectors`.`id` = `pLog`.`inspector`
WHERE
`mhr_patients`.`status` = 5
AND `mhr_patients`.`fullName` LIKE '%aaa%'
OR `mhr_patients`.`nationalCode` LIKE '%aaa%'
OR `mhr_patients`.`fileNumber` LIKE '%aaa%'
OR `mhr_patients`.`age` LIKE 'aaa'
OR `mhr_patients`.`firstGCS` LIKE 'aaa'
OR `mhr_patients`.`patientDetail` LIKE '%aaa%'
GROUP BY `pLog`.`pId`
ORDER BY `mhr_patients`.`inspectorRegisterTime` asc
LIMIT 30
What is the problem?
Upvotes: 0
Views: 40
Reputation: 57721
You probably intended:
`mhr_patients`.`status` = 5
AND (`mhr_patients`.`fullName` LIKE '%aaa%'
^ -- bracket here
OR `mhr_patients`.`nationalCode` LIKE '%aaa%'
OR `mhr_patients`.`fileNumber` LIKE '%aaa%'
OR `mhr_patients`.`age` LIKE 'aaa'
OR `mhr_patients`.`firstGCS` LIKE 'aaa'
OR `mhr_patients`.`patientDetail` LIKE '%aaa%' ) <-- close here
You want status = 5 or a textual match
. Without the brackets MySQL interprets as statsus = 5 or textual match in fullName, or other textual match
.
Upvotes: 0
Reputation: 6592
You are mixing and
and or
in your query. This will basically negate your where statement. You need to use parenthesis to group your logic
WHERE
`mhr_patients`.`status` = 5
AND
(`mhr_patients`.`fullName` LIKE '%aaa%'
OR `mhr_patients`.`nationalCode` LIKE '%aaa%'
OR `mhr_patients`.`fileNumber` LIKE '%aaa%'
OR `mhr_patients`.`age` LIKE 'aaa'
OR `mhr_patients`.`firstGCS` LIKE 'aaa'
OR `mhr_patients`.`patientDetail` LIKE '%aaa%' )
GROUP BY `pLog`.`pId`
ORDER BY `mhr_patients`.`inspectorRegisterTime` asc
LIMIT 30
You likely intended to have all patients with status of 5 and a either full name like %aaaa% or nationalCode like %aaa$, etc. To accomplish this, you need to use parenthesis to group your or statements together into a single unit.
Upvotes: 1