Link to home
Start Free TrialLog in
Avatar of ChoobsTech
ChoobsTechFlag for Switzerland

asked on

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 ;)
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

you cannot dynamically join to a database based on a column value...
so you must stick with 2 queries...
ASKER CERTIFIED SOLUTION
Avatar of albuitra
albuitra
Flag of Colombia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial