473,324 Members | 2,179 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

which join to use?

Hi,

I have three tables that I would like to join: employee, manager_emp and manager. I would like to see all employees that have managers, along with the manager's name that can be derived from manager table. If an employee does not have a manager then just display NULL. However, I can not figure out how to join third table (manager) and still get the NULLS.



SELECT emp.id, emp.name, emp.dept, mgre.mgrid
FROM employee AS emp
LEFT JOIN manager_emp AS mgre on emp.id = mgre.id

+-----+---------+--------+--------+
| id | name | dept | mgrid |
|-----+---------+--------+--------+
| 3 | fred | IT | m003 |
| 6 | fredola | RISK | m002 |
| 7 | reddy | NULL | NULL |


This is the output I would like:

+-----+---------+--------+--------+---------+
| id | name | dept | mgrid | mgrname |
|-----+---------+--------+--------+---------+
| 3 | fred | IT | m003 | j james |
| 6 | fredola | RISK | m002 | b mike |
| 7 | reddy | NULL | NULL | NULL |

Any assistance would be appreciated.
Feb 8 '07 #1
4 1562
One possible solution to this is to relax the constraint on the manager table and insert a column where the mgrid is null. I don't if this is a correct solution.
Feb 8 '07 #2
ronverdonk
4,258 Expert 4TB
At first sight this is a JOIN solution. But, in order to handle this properly, we have to know the structure of the 3 tables involved. How else could we find a relation? So show us the columns of the 3 tables involved.

Ronald :cool:
Feb 8 '07 #3
i've had to created another account as the password reset facility is not working. here's the table structure

employee

id int(4),
name varchar (40),
dept varchar (30),
bldgcode varchar(5),
email varcahr(128)

manager_emp

id int(4)
mgrid varchar(10)


manager

mgrid varchar(10),
name varchar(40),
bldgcode varchar(5),
email varcahr(128),
costbucker varchar(10)


cheers
Feb 8 '07 #4
ronverdonk
4,258 Expert 4TB
Have a go at this one:
Expand|Select|Wrap|Line Numbers
  1. mysql> select * from employee;
  2. +------+---------+------+----------+-------+
  3. | id   | name    | dept | bldgcode | email |
  4. +------+---------+------+----------+-------+
  5. |    3 | fred    | IT   |          |       |
  6. |    6 | fredola | RISK |          |       |
  7. |    7 | reddy   | ABC  |          |       |
  8. +------+---------+------+----------+-------+
  9. mysql> select * from manager_emp;
  10. +------+-------+
  11. | id   | mgrid |
  12. +------+-------+
  13. |    3 | M01   |
  14. |    7 | M03   |
  15. +------+-------+
  16. mysql> select * from manager;
  17. +-------+----------+----------+-------+------------+
  18. | mgrid | name     | bldgcode | email | costbucker |
  19. +-------+----------+----------+-------+------------+
  20. | M01   | Manager1 |          |       |            |
  21. | M03   | Manager3 |          |       |            |
  22. +-------+----------+----------+-------+------------+
  23.  
Expand|Select|Wrap|Line Numbers
  1. SELECT e.id,e.name,dept,manager_emp.mgrid,manager.name FROM employee as e 
  2. LEFT JOIN manager_emp ON(e.id=manager_emp.id) 
  3. LEFT JOIN manager ON(manager_emp.mgrid=manager.mgrid);
  4. +------+---------+------+-------+----------+
  5. | id   | name    | dept | mgrid | name     |
  6. +------+---------+------+-------+----------+
  7. |    3 | fred    | IT   | M01   | Manager1 |
  8. |    6 | fredola | RISK | NULL  | NULL     |
  9. |    7 | reddy   | ABC  | M03   | Manager3 |
  10. +------+---------+------+-------+----------+
  11.  
Ronald :cool:
Feb 11 '07 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Pierre Fortin | last post by:
This quest for understanding started very innocently... A simple error on my part, passing on args as "args" instead of "*args" to os.path.join() led me to wonder why an error wasn't raised... ...
0
by: B. Fongo | last post by:
I learned MySQL last year without putting it into action; that is why I face trouble in formulating my queries. Were it a test, then you would have passed it, because your queries did help me...
2
by: Martin | last post by:
I am now working on SQL Server 2000 having had previous experience on a different database. Both of the OUTER JOIN syntaxes is different from what I am used to and I am finding it slightly...
3
by: Ian Boyd | last post by:
i know nothing about DB2, but i'm sure this must be possible. i'm trying to get a client to create a view (which it turns out is called a "Logical" in DB2). The query needs a LEFT OUTER JOIN, but...
1
by: Justin | last post by:
Hello, I have a scenario where I have unique identifiers in about 25 tables, each table has varying fields - eg. Table #1 Table #2 Table #3 Table #4 --------- ...
14
by: Bob | last post by:
I have a function that takes in a list of IDs (hundreds) as input parameter and needs to pass the data to another step as a comma delimited string. The source can easily create this list of IDs in...
1
by: ehchn1 | last post by:
Hi, Just curious. Would you use ANSI style table joining or the 'old fashion' table joining; especially if performance is the main concern? What I meant is illustrated below: ANSI Style...
52
by: MP | last post by:
Hi trying to begin to learn database using vb6, ado/adox, mdb format, sql (not using access...just mdb format via ado) i need to group the values of multiple fields - get their possible...
3
by: jagadeesh kumar | last post by:
While joining like the below, select * from EMPLOYEE e , position p where e.empid=p.empid select * from EMPLOYEE e inner join position p where on e.empid=p.empid performance wise, which...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.