SQL link 5 tables

Question

Much more to it but there are 3 tables...

employees  
employee_id  

licenses  
license_id  
employee_id  


companies  
company_id  
employee_id  

SELECT  * FROM employees  
LEFT JOIN licenses  
ON employees.employee_id = licenses.employee_id   
LEFT JOIN companies  
ON employees.employee_id = companies.employee_id  
GROUP BY employees.employee_id  

I can only get it so either licenses OR companies returns a value and the other returns NULL. How can I get it so BOTH tables return values? Seems so easy but it isn't working in this case and I can't figure out why...

EDIT: Here is some more info.
Not every employee has a license.
Not every employee has a company.

Would like to return employee_id license_id (if exists, else NULL) company_id (if exists, else NULL)

Take the case where an employee has both a license_id and a company_id. By removing one of the JOIN clauses, can return the corresponding value. However, when both are combined, only return the company_id and license_id returns NULL.

Weird, right? Any ideas or is more info needed?

DATA:
employee_id
1
2
3

employee_id license_id 1 1
2 1
3 2

employee_id company_id 1 1
2 1
3 2

SORRY FOR WASTING TIME

The table schema is screwed up and redundant. This was an inherited schema and I was just considering the SQL, not the underlying structure. Database needs restructuring.

Answer 1

There are alot of different JOIN types. My suggestion would be to do some reading about them to figure it out. You can use a FULL OUTER JOIN

SELECT  * 
FROM employees  
FULL OUTER JOIN licenses  
    ON employees.employee_id = licenses.employee_id   
FULL OUTER JOIN companies  
    ON employees.employee_id = companies.employee_id  
GROUP BY employee_id  

It is difficult to figure exactly what you want without seeing any data. But here is a handy visual explanation of SQL Joins. I keep it bookmarked for those times that I need some help.

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Answer 2

This is very difficult to answer without seeing the data in the tables. So I'll make the assumption, that there are rows in each table that all have a single Employee_ID that is the same, so the joins work. While your testing this I would suggest picking one Employee_id to work with too, just to simplify the output while you test.

Based on my assumptions, I switched your queries to inner joins, this will only show rows that match on the Employee_id. I also used "aliasing". The single letter I put after each table pointer saves a lot of typing.

SELECT *
FROM  employees e
INNER JOIN licenses l
    ON e.employee_id = l.employee_id
INNER JOIN companies 
    ON e.employee_id = c.employee_id
GROUP BY   employee_id

If you're new to SQL joins, this article may be helpful too. Best of luck!

Answer 3

This is my favorite article on the subject.

http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx

Try

FULL OUTER JOIN