Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1370
  • Last Modified:

Mysql table alias as a subquery?

Dear experts,

I have an internal and an external employee database
DB: External
TABLE: employee
idemployee  INT
Name VARCHAR(30)

DB: Internal
TABLE: employee
idemployee  INT
Name VARCHAR(30)

When making an autosuggest I perform a simple UNION to search through both.

To store a reponsible of a project it is simple:

TABLE: responsible
idtask INT
idresponsible INT
idemployee  INT
db VARCHAR(30)


To show the values again, I am currently doing the following:

//FETCH DB
$q="SELECT * FROM responsible WHERE idtask='11' ";
$query=mysql_query($q);
if($r = mysql_fetch_array($query))
{
  $db=$r['db'];
  $idempl=$r['idemployee '];
}

//FETCH employee name
$q="SELECT name FROM ".$db.".employee WHERE idemployee='874949'";
$query=mysql_query($q);
if($r = mysql_fetch_array($query))
{
  $name=$r['name'];
}

It works, but I would like to make one single query of the 2 queries above...

I tried:
SELECT * FROM (SELECT CONCAT(db,".","employee")  FROM responsible WHERE idtask=11) as e, responsible as r WHERE e.idemployee=r.idemployee
-> internal.employee
I expected a list of employee names...

Any ideas would be welcome ;)
0
ChoobsTech
Asked:
ChoobsTech
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot dynamically join to a database based on a column value...
so you must stick with 2 queries...
0
 
albuitraCommented:

SELECT * FROM 
(SELECT * from Internal.employee WHERE idtask=11
 union 
select * from External.employee WHERE idtask=11 )  as e

Open in new window

0
 
virmaiorCommented:
or more efficiently.  
using a UNION will be more efficient in this case then a subquery.  (so it is still basically two queries but returns only one result set).
SELECT 'internal' as source, employee.* FROM responsible LEFT JOIN  internal.employee ON employee.idemployee = responsible.idemployee WHERE responsible.taskid = 11
UNION
SELECT 'external' as source, employee.* FROM responsible LEFT JOIN external.employee ON employee.idemployee = responsible.idemployee  WHERE responsible.taskid = 11

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now