Page 1 of 1

QUERY TROUBLE

Posted: Wed Nov 25, 2009 11:06 am
by koolsamule
Hi Chaps,

I have a QUERY:

Code: Select all

SELECT              
tbl_projects.projid, 
tbl_projects.projtitle, 
tbl_projects.projdue, DATE_FORMAT(tbl_projects.projdue, '%%d/%%m/%%Y') as projdue_format, 
tbl_projects.projtype,  
tbl_projects.projinvtype, 
tbl_projects.FK_custid, 
tbl_languaget.langtname,
tbl_doctype.doctypename,
tbl_jobs.jobid, 
tbl_jobs.FK_projid,               
tbl_jobs.jobname,               
tbl_jobs.FK_langid,               
tbl_jobs.jobpages,               
tbl_jobs.jobshipped, 
tbl_jobs.jobinvsent,    
tbl_jobs.jobtranslatorcharge,     
'tbl_jobs' as fromtable,
tbl_customers.custid,
FROM tbl_projects   
INNER JOIN tbl_jobs               
ON tbl_projects.projid=tbl_jobs.FK_projid   
INNER JOIN tbl_languaget               
ON tbl_languaget.langtid=tbl_jobs.FK_langid   
INNER JOIN tbl_customers               
ON tbl_customers.custid=tbl_projects.FK_custid 
INNER JOIN tbl_doctype
ON tbl_doctype.doctypeid=tbl_jobs.FK_doctypeid
WHERE tbl_projects.projstatus='Complete'         
AND tbl_projects.projinvtype='Costing Sheet'
I want to add another column called 'translator', but the value could come from either:
tbl_inhouseuser
OR
tbl_freelancer
The trouble I'm having is that I guess I would have to use a CASE, something like this:

Code: Select all

CASE WHEN tbl_inhouseuser.userid=tbl_jobs.FK_userid
         THEN tbl_inhouseuser.username
         ELSE tbl_freelancer.freename
         END as translator
...but I got an error, stating the table tbl_inhouseuser / tbl_freelancer could not be found.
If I try to add the two tables to the INNER JOIN, the recordset returns empty....as the translator can only come from one of the tables, not both.
It's probably something simple, but I can't seem to find the right way to do it!

Re: QUERY TROUBLE

Posted: Thu Nov 26, 2009 4:29 am
by sergio-pro
Hi

Try to add it using LEFT OUTER JOIN

Re: QUERY TROUBLE

Posted: Thu Nov 26, 2009 4:45 am
by koolsamule
Hi, thanks for the reply, which table should I add as LEFT OUTER JOIN, the tbl_user_main, tbl_freelancer or both? Cheers

Re: QUERY TROUBLE

Posted: Thu Nov 26, 2009 4:58 am
by sergio-pro
both

Re: QUERY TROUBLE

Posted: Thu Nov 26, 2009 5:13 am
by koolsamule
something like this?:

FROM tbl_projects
INNER JOIN tbl_jobs
ON tbl_projects.projid=tbl_jobs.FK_projid
INNER JOIN tbl_languaget
ON tbl_languaget.langtid=tbl_jobs.FK_langid
INNER JOIN tbl_customers
ON tbl_customers.custid=tbl_projects.FK_custid
INNER JOIN tbl_doctype
ON tbl_doctype.doctypeid=tbl_jobs.FK_doctypeid
LEFT OUTER JOIN tbl_user_main
ON tbl_user_main.userid=tbl_jobs.FK_usertranslationid
INNER JOIN tbl_freelancer
ON tbl_freelancer.freeid=tbl_jobs.FK_freeid

Re: QUERY TROUBLE

Posted: Thu Nov 26, 2009 5:19 am
by sergio-pro
No, you need a value from one of tables
tbl_inhouseuser.username or tbl_freelancer.freename
so you have to add them both:

Code: Select all

 
FROM tbl_projects 
  INNER JOIN ... (other joins)
  LEFT OUTER JOIN tbl_inhouseuser ON tbl_inhouseuser.FKEY = table-to-join.key 
  LEFT OUTER JOIN tbl_freelancer ON tbl_freelancer.FKEY = table-to-join.key 
 

Re: QUERY TROUBLE

Posted: Thu Nov 26, 2009 5:21 am
by koolsamule
Oh OK, thanks for that, I'll give it a go now!

Re: QUERY TROUBLE

Posted: Thu Nov 26, 2009 5:37 am
by koolsamule
Sweeeet, cheers dude!