QUERY TROUBLE

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
koolsamule
Forum Contributor
Posts: 130
Joined: Fri Sep 25, 2009 10:03 am

QUERY TROUBLE

Post 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!
User avatar
sergio-pro
Forum Commoner
Posts: 88
Joined: Sat Dec 27, 2008 12:26 pm

Re: QUERY TROUBLE

Post by sergio-pro »

Hi

Try to add it using LEFT OUTER JOIN
koolsamule
Forum Contributor
Posts: 130
Joined: Fri Sep 25, 2009 10:03 am

Re: QUERY TROUBLE

Post 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
User avatar
sergio-pro
Forum Commoner
Posts: 88
Joined: Sat Dec 27, 2008 12:26 pm

Re: QUERY TROUBLE

Post by sergio-pro »

both
koolsamule
Forum Contributor
Posts: 130
Joined: Fri Sep 25, 2009 10:03 am

Re: QUERY TROUBLE

Post 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
User avatar
sergio-pro
Forum Commoner
Posts: 88
Joined: Sat Dec 27, 2008 12:26 pm

Re: QUERY TROUBLE

Post 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 
 
koolsamule
Forum Contributor
Posts: 130
Joined: Fri Sep 25, 2009 10:03 am

Re: QUERY TROUBLE

Post by koolsamule »

Oh OK, thanks for that, I'll give it a go now!
koolsamule
Forum Contributor
Posts: 130
Joined: Fri Sep 25, 2009 10:03 am

Re: QUERY TROUBLE

Post by koolsamule »

Sweeeet, cheers dude!
Post Reply