select with joins

[mufka]

I'm trying to work out a select statement that will pull data from a few different tables based on common id's across the tables.

My test code is:

select
     c.firstname||' '||c.lastname,
     d.dept_name,
     s.salary_level,
     su.supervisor_name,
          from contact c, dept d, salary s, supervisor su
          where c.id=su.c_id
          and d.dept_id=c.dept_id
          and s.salary_id=c.salary_id
    order by c.lastname
;

In this case, the four tables are contact, dept, salary and supervisor. The problem that I am having is that I want to list all first and last names, but some don't have departments assigned so with the current select statement, those entries would be excluded. How can I adjust my select to still list those that don't have departments but maybe put a - where no department exists?

I can get this to work in Oracle with:

select
     c.firstname||' '||c.lastname,
     d.dept_name,
     s.salary_level,
     su.supervisor_name,
          from contact c, dept d, salary s, supervisor su
          where c.id=su.c_id
          and d.dept_id(+)=c.dept_id
          and s.salary_id=c.salary_id
    order by c.lastname
;

But this doesn't work in MySQL.

 

[AdaHacker]

Sounds like you want an outer join. Perhaps something like this:

select
     c.firstname||' '||c.lastname,
     d.dept_name,
     s.salary_level,
     su.supervisor_name,
          from contact c 
          LEFT JOIN dept d ON d.dept_id=c.dept_id
          LEFT JOIN salary s ON s.salary_id=c.salary_id 
          LEFT JOIN supervisor su ON c.id=su.c_id
    order by c.lastname
;

 

[r937]

only one LEFT OUTER is needed

  FROM contact c 
INNER
  JOIN salary s 
    ON s.salary_id = c.salary_id 
INNER
  JOIN supervisor su 
    ON su.c_id = c.id
LEFT OUTER
  JOIN dept d 
    ON d.dept_id = c.dept_id

note that the columns used to join c and su don't make sense (assuming a supervisor can have more than one contact)

mysql uses the CONCAT function, not the double pipes operator (||)

you might also want to remove the dangling comma before the FROM keyword ;-)

r937.com | rudy.ca