I have 15 records (jobs) in table A. These records (jobs) are also present in table B and table C only if they are closed (and have a date).
I need to show the 15 records from the table A independent of whether they are present in table B or table C. Right now, one of the records from Table A has a closing date in table B.
My queries so far read as following:
SELECT pruebas.prueba AS cod, pruebas.numeroPrueba AS num, pruebas.descripcion AS des, pruebas.prioridad AS priority, resultados.fecha AS dat FROM pruebas LEFT JOIN resultados ON pruebas.prueba = resultados.prueba where (pruebas.tipo='01' AND pruebas.servicio='01JOG') ORDER BY num
The query runs fine on two tables but is still not complete as there is another table involved -- table C. Table B and table C are quite similar, but table B is populated by a user and table C by the manager. If the USER is the last person to evaluate the job, the closing date is obtained from table B. If the manager is the last person to evaluate the job, the closing date is obtained from table C.
Any ideas how I can use a left join on three tables simultaneously?
This can be accomplished rather simply by using parentheses to first join two tables, and then join the third to the result. Here is an example:
create table a (id numeric, name varchar(30)); create table b (id numeric, name varchar(30)); create table c (id numeric, name varchar(30)); insert into a values (1,'soft stuff'); insert into a values (2,'green stuff'); insert into a values (3,'heavy stuff'); insert into a values (4,'warm stuff'); insert into b values (1,'Frank'); insert into b values (3,'Bill'); insert into c values (1,'Mary'); insert into c values (4,'Sue'); select a.id, b.name, c.name from (a left join b on a.id = b.id) left join c on a.id = c.id; ID NAME NAME -- ------ ------ 1 Frank Mary 2 NULL NULL 3 Bill NULL 4 NULL Sue
For More Information
- Dozens more answers to tough SQL questions from Jason Law are available here.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.