QUERY TROUBLE
Posted: Wed Nov 25, 2009 11:06 am
Hi Chaps,
I have a QUERY:
I want to add another column called 'translator', but the value could come from either:
...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!
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'The trouble I'm having is that I guess I would have to use a CASE, something like this:tbl_inhouseuser
OR
tbl_freelancer
Code: Select all
CASE WHEN tbl_inhouseuser.userid=tbl_jobs.FK_userid
THEN tbl_inhouseuser.username
ELSE tbl_freelancer.freename
END as translatorIf 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!