Re: Help on Outer Join needed

From: Martyn Cavett <cavett_at_anonymous.com>
Date: 1997/05/31
Message-ID: <01bc6e01$1dab00a0$48567ec2@cavett.globalnet.co.uk>#1/1

Marcelo -

There is 'more than one way to skin a cat' of course but below is a pragmatic solution. I believe any approach using pure SQL will run into the same problems as you get from your quoted SELECT statement, even if VIEWS are used.

The result you want can be got procedurally using PL/SQL and a temporary table to store the results (though I guess you could use DBMS_OUTPUT or DBMS_PIPE to output the results interactively). I suggest the following solution:

Create a results table with structure:

CREATE TABLE t_results

(t_emp		VARCHAR2(30)
,t_tel		VARCHAR2(30)
,t_class 	VARCHAR2(30))

The following PL/SQL standalone should do the trick. . .

DECLARE
/* Cursor to get each employee in alpha order*/
CURSOR c1 IS

	SELECT e.emp
	FROM    employee e
	ORDER BY 1;

/*Cursor to select back ordered phone numbers*/
CURSOR c2 (c2_emp IN employee.emp%ROWTYPE) IS

	SELECT p.tel
	FROM    phone p
	WHERE p.emp = c2_emp
	ORDER BY 1;

/*Cursor to select back ordered courses */
CURSOR c3(c3_emp IN employee%ROWTYPE) IS

	SELECT c.class
	FROM    class c
	WHERE c.emp = c3_emp
	ORDER BY 1;

p_counter	NUMBER(10);
i		NUMBER(10);
p_tel		phone.tel%TYPE;
p_class		class.class%TYPE;	

BEGIN
/* clear down results table */

        DELETE FROM t_results;

/* Use a cursor FOR loop to get each employee name to process */

	<< outer >>
	FOR emp_record IN c1 LOOP

	/* Get max no. of tel.nos or classes for 
	   inner loop counter */
	SELECT LARGEST(COUNT(p.emp),COUNT(c.emp))
	INTO       p_counter
	FROM	  phone p
	,	  class  c
	WHERE  p.emp = emp_record.emp
	AND       c.emp = emp_record.emp; 

	OPEN c2;
	OPEN c3;

	<< inner >>
	/* Loop for whichever the greater phone or class 
	   each time trying to fetch a row
	   from each cursor.  NULL value is assigned to
	   the variables if non returned (for safety).
	   	Insert a row into the results table
	   	so building up the desired results - only 1 row
	   	for each distinct value */
 	FOR i IN 1..p_counter LOOP;

		FETCH c2(emp_record.emp) INTO p_tel;
		IF SQL%NOTFOUND THEN
			p_tel := NULL;
		END IF;
		FETCH c3(emp_record.emp) INTO p_class;
		IF SQL%NOTFOUND THEN
			p_class := NULL;

		INSERT INTO t_results
		(t_emp
		,t_tel
		,t_class)
		VALUES	
		(emp_record.emp
		,p_tel
		,p_class);

		END inner;

	CLOSE c2;
	CLOSE c3;
	END outer;

	COMMIT;

EXCEPTION
	WHEN OTHERS THEN
		IF c2%ISOPEN THEN
		CLOSE c2;
		END IF;
		IF c3%ISOPEN THEN
		CLOSE c3;
		END IF;
	RAISE;

END;
/

IN SQL*Plus:

        SELECT * FROM t_results;

Marcelo Fontinha <ftvr_at_sprynet.com> wrote in article <338f1ec5.21604233_at_news.sprynet.com>...

> I need to join the following three tables and obtain the result shown
> below. The result is a list of all the employee's phones and classes
> they teach. Phones and classes ARE NOT related directly.
> I am using Oracle 7.3 and PowerBuilder 5.0.02.
> 
> 
> I tried this SQL with two outer joins but a get a cartesian product.
> See  result set below.
> 
> SELECT distinct e.emp, p.tel, c.class
>   FROM employee e,
>        phone p,
>        class c
>  WHERE e.emp = p.emp (+)
>    AND e.emp = c.emp (+)
> 
> 
> 
> Is this possible to accomplish at all ?
> Will it require the use of views and/or temporary tables and/or stored
> procedures ?
> Am I missing something pretty stupid or just brain dead ?
> 
> Note: I was able to create a nested report in PowerBuilder and got the
> desired result but I don't want to use this approach because of
> performance issues.
> 
> Any comments appreciated.
> 
> Marcelo
> 
> 
> 
> TABLES AND SAMPLE DATA
> ======================
> 
> employee    Phone            class
> --------    -----            -----
> 
> emp         emp tel          emp class
> ---         --- --------     --- -------
> bob         bob 555-5555     Bob SQL I
> ted         bob 666-6666     Bob SQL II
> joe         bob 777-7777     Bob DBA I
> pam         ted 111-1111     Ted ADM. I
> tim         ted 222-2222     Ted ADM. II
>             ted 333-3333     Joe SQL I
>             joe 444-4444     Joe DBA I
>             tim 888-8888     Pam SQL I
>             tim 999-9999     Pam SQL II
> 
> 
> RESULT WANTED (ignore blank rows)
> =============
> 
> emp tel      class
> --- -------- -------
> Bob 555-5555 SQL I
> Bob 666-6666 SQL II
> Bob 777-7777 DBA I
> 
> Ted 111-1111 ADM. I
> Ted 222-2222 ADM. II
> Ted 333-3333
> 
> Joe 444-4444 SQL I
> Joe          DBA I
> 
> Pam          SQL I
> Pam          SQL II
> 
> Tim 888-8888
> Tim 999-9999
> 
> 
> RESULT OBTAINED  (ignore blank rows)
> =============== 
> 
> emp tel      class
> --- -------- -------
> Bob 555-5555 SQL I
> Bob 555-5555 SQL II
> Bob 555-5555 DBA I
> Bob 666-6666 SQL I
> Bob 666-6666 SQL II
> Bob 666-6666 DBA I
> Bob 777-7777 SQL I
> Bob 777-7777 SQL II
> Bob 777-7777 DBA I
> 
> Ted 111-1111 ADM. I
> Ted 111-1111 ADM. II
> Ted 222-2222 ADM. I
> Ted 222-2222 ADM. II
> Ted 333-3333 ADM. I
> Ted 333-3333 ADM. II
> 
> Joe 444-4444 SQL I
> Joe 444-4444 DBA I
> 
> Pam          SQL I
> Pam          SQL II
>                           
> Tim 888-8888
> Tim 999-9999
> 
> 
> 
> 

Received on Sat May 31 1997 - 00:00:00 CDT