CODEWITHSUNDEEP
databasedatabase-designrelational-database
Momen Zalabany
Momen Zalabany

Reputation: 9007

mysql complex select query from multiple tables

Table Visits;

fields[id,patient_id(fk),doctor_id(fk),flag(Xfk),type(Xfk),time_booked,date,...]

Xfk = it refer to other table, but its not a must to exist so i dont use constrain.

SELECT `v`.`date`, `v`.`time_booked`, `v`.`stats`, `p`.`name` as pt_name, 
 `d`.`name` as dr_name, `f`.`name` as flag_name, `f`.`color` as flag_color,
 `vt`.`name` as type, `vt`.`color` as type_color

FROM (`visits` v, `users` p, `users` d, `flags` f, `visit_types` vt)

WHERE `p`.`id`=`v`.`patient_id` 
AND `d`.`id`=`v`.`doctor_id` 
AND `v`.`flag`=`f`.`id` 
AND `v`.`type`=`vt`.`id`
AND `v`.`date` >= '2013-02-27'
AND (v.date <= DATE_ADD('2013-02-27', INTERVAL 7 DAY))
AND (`v`.`doctor_id`='00002' OR `v`.`doctor_id`='00001')
ORDER BY `v`.`date` ASC, `v`.`time_booked` ASC;

One big statmeant i have ! my question is,

1: should i consider using join instead of select multiple tables ? and if i should why ?

this query execution time is 0.0009 so i think its fine, and since i get all my data in one query, or is it bad practice ?

2: in the select part i want to say

if v.type != 0 select f.name,f.color else i dont want to select them nither there tables flags f is it possible ?

also currently if flag was not found, it replicate all rows as much as flag table have in rows ! is there a way i can prevent this ? both for flag and visit_types table ?

Upvotes: 0

Views: 131

Answers (1)

ryan1234
ryan1234

Reputation: 7275

  1. If it's running fast, I wouldn't mess with it. I generally prefer to use joins instead of matching stuff in the where clause.

  2. Any chance you'd remove the ` characters? Just makes it a bit harder to read in my opinion.

  3. Look at the case statement for MySQL: http://dev.mysql.com/doc/refman/5.0/en/case.html


select case when v.type <> 0 then 
   f.name 
else 
   '' 
end as name, ...

Upvotes: 1

Related Questions