Solved

Mysql table alias as a subquery?

Posted on 2008-06-18
3
1,314 Views
Last Modified: 2013-12-12
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
Comment
Question by:ChoobsTech
3 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21816210
you cannot dynamically join to a database based on a column value...
so you must stick with 2 queries...
0
 
LVL 4

Accepted Solution

by:
albuitra earned 350 total points
ID: 21816250

SELECT * FROM 

(SELECT * from Internal.employee WHERE idtask=11

 union 

select * from External.employee WHERE idtask=11 )  as e

Open in new window

0
 
LVL 20

Assisted Solution

by:virmaior
virmaior earned 150 total points
ID: 21821888
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now