Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1358
  • 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

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

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