| Re: comparing arrays [message #181060 is a reply to message #181054] |
Sun, 07 April 2013 19:33   |
Thomas 'PointedEars'
Messages: 701
Registered: October 2010
Karma:
|
Senior Member |
|
|
bill wrote:
> On 4/7/2013 10:14 AM, Frank Steinmetzger wrote:
>> You select two different appointments that have the same date:
>>
>> SELECT s1.patient_number AS pn, s1.time AS t1, s2.time AS t2,
>> s1.duration AS d1, s2.duration AS d2
>> FROM schedule s1, schedule s2
>> WHERE s1.time=s2.time AND s1.time=<today's date>
>> AND s1.patient_number=s2.patient_number
>> AND s1.patient_number=<ID of patient of interest>
>> AND s1.id!=s2.id
>> AND s1.doctor_number!=s2.doctor_number
>>
>> Of course, those times don't work that way, b/c they have date *and*
>> time, and you want to deal with date only. I'm not into advanced SQL,
>> perhaps there is an SQL function to only extract and use the date part.
>
> there is
>>
>> You then would need to PHP-fiddle the time and duration together for only
>> those appointments that are today in a nested for-loop.
> select s1.user as s1user,
> s2.user as s2user,
> s1.apptTime as s1apptTime,
> s2.apptTime as s2pptTime,
> users.physician,
> users.user_id
> from schedule as s1 join schedule as s2 join users
> on (s1.patient_number = s2.patient_number
> and users.user_id = s2.user)
>
> where s1.patient_number = s2.patient_number
> and s1.appt_status = 'a'
> and s2.appt_status = 'a'
> and s1.patient_number = '5214'
> and s1.schedule_id != s2.schedule_id
> and physician = '1'
> and date(s1.apptTime) = date(s2.apptTime)
> and date(s1.apptTime) = '2013-04-03'
>
> did the job
SELECT s1.user AS s1user,
s2.user AS s2user,
s1.apptTime AS s1apptTime,
s2.apptTime AS s2pptTime,
users.physician,
users.user_id
FROM
(schedule AS s1 INNER JOIN schedule AS s2
ON (s1.patient_number = s2.patient_number
AND s1.appt_status = s2.appt_status)) AS tmp
INNER JOIN users
ON (users.user_id = tmp.user)
WHERE s1.appt_status = 'a'
AND s1.patient_number = 5214
AND s1.schedule_id != s2.schedule_id
AND physician = 1
AND DATE(s1.apptTime) = DATE(s2.apptTime)
AND DATE(s1.apptTime) = '2013-04-03'
probably does it better.
- Write queries so that you can tell names, keywords and function
calls apart. It is customary to write keywords and function
identifiers all-uppercase, and keep names either all-lowercase
(e.g., in MySQL and SQLite) or CamelCase (e.g., in MS SQL).
- Quote all names to avoid them being considered keywords, causing
a syntax error. (Since the SQL dialect is unknown, no recommendation
can be made how to quote.)
- Write explicitly what kind of JOIN it is.
- Add a condition for each single JOIN, so that the result set the next
table needs to be joined with is smaller.
- If you JOIN tables on values they have in common, it makes
no sense to filter for them again.
- JOIN on common values, filter (WHERE) on different ones, so that the
result set that needs to be filtered is smaller.
- Your keys are probably numbers. Do not write them as strings, then.
- You almost never want to write a query in PHP like this; see
“SQL injection”.
Further optimizations (like USING) are possible with MySQL (on-topic in
comp.databases.mysql).
> Thanks for the great suggestion.
You are welcome.
PointedEars
--
Anyone who slaps a 'this page is best viewed with Browser X' label on
a Web page appears to be yearning for the bad old days, before the Web,
when you had very little chance of reading a document written on another
computer, another word processor, or another network. -- Tim Berners-Lee
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]