ChoobsTech
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 ;)
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 ;)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
so you must stick with 2 queries...