How best to construct this query

[Claus Mygind]

I wish to construct a query using 4 tables.
time2 t
job j
jbook b
master m

the last table master is linked to both job and jbook so it can provide a client name for a specific job

both job and jbook are linked to a employee's time record.

I want to return from either the job or jbook tables the project name and city.

I also need the client name from the master table that is linked to either the job or jbook table.

For most of the time the information will come from the job file. Without getting into to much detail, there are times that the job does not yet exist in the table so the information has to be retrieved from the jbook table (as this is unverified information it is only to be used when not found in the job table).

I am pretty sure I want some sort of "on" clause in my select statement, where "on j.jobid is null" then use b. info...

This is what I have constructed, but I think it can be written better. So any help is appreciated:

select t.jobid, t.prjctname, j.prjctcity, m.name, t.workday,
, concat(b.prjctcity) as bCity, concat(m2.name) as bName 
from `time2` t 
left join `job` j on j.jobid=t.jobid 
left join `master` m on m.id=j.id 
left join `jbook` b on b.jobid=t.jobid 
left join `master` m2 on m2.id=b.id 
where t.empNo = "999" 
   and substr(t.jobid,1,1) between "0" and "9"
order by t.workday desc limit 50
 

[code green]

Looks OK at first glance.
You could use USING() instead of ON to simplify the syntax.

Joining to the same table twice is usually done when
the data is required under two different conditions.
You are joining because it links to two tables which doesn't seem neccessary because an ON statement can link to more than one table

[prabirchoudhury]

hey
i dont know why you are using concat() function when you are not concatenating strings at all..


MySQL CONCAT Function

[Claus Mygind]

@prabirchoudhury
Because I have similar name fields in both tables. Note the following:

j.prjctcity --- concat(b.prjctcity) as bCity

m.name --- concat(m2.name) as bName

referencing either prjctcity or name would generate and error. So you have to be more specific.