Q
Problem solve Get help with specific problems with your technologies, process and projects.

Outer join with three or more tables

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


This was last published in August 2002

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close