>>SELECT People.cID,; >> People.LName,; >> People.FName,; >> Home.Phone AS Home,; >> Office.Phone AS Office,; >> Cell.Phone AS Cell,; >>.... >>FROM People; >>LEFT JOIN Phones Home ON People.cId == Home.cId AND Home.Type =='Home'; >>LEFT JOIN Phones Office ON People.cId == Home.cId AND Office.Type =='Office'; >>LEFT JOIN Phones Cell ON People.cId == Home.cId AND Cell.Type =='Cell' ; >>.... >>>
SET NULLDISPLAY TO '' CREATE CURSOR People (cID INT,LName VARCHAR(20),FName VARCHAR(30)) INSERT INTO People VALUES (1,'Naomi','Nosonovsky') CREATE CURSOR Phones (cID INT,row INT,Type CHAR(10),Phone VARCHAR(30)) INSERT INTO Phones VALUES (1,1,'Home','HOMETEL 1') INSERT INTO Phones VALUES (1,2,'Home','HOMETEL 2') INSERT INTO Phones VALUES (1,1,'Cell','CELLULAR 1') INSERT INTO Phones VALUES (1,2,'Cell','CELLULAR 2') INSERT INTO Phones VALUES (1,3,'Cell','CELLULAR 3') INSERT INTO Phones VALUES (1,4,'Office','OFFICE 4') INSERT INTO Phones VALUES (1,2,'Office','OFFICE 2') SELECT ; People.cID ; , People.LName ; , People.FName ; , NVL(Home.row,NVL(Cell.row,Office.row)) Row; , Home.Phone Home ; , Cell.Phone Cell ; , Office.Phone Office ; FROM (SELECT * FROM Phones WHERE Type ='Home') Home ; FULL JOIN (SELECT * FROM Phones WHERE Type ='Cell') Cell ON Home.cId = Cell.cId AND Home.row = Cell.row ; FULL JOIN (SELECT * FROM Phones WHERE Type ='Office') Office ON Home.cId = Office.cId AND Home.row = Office.row ; JOIN People ON People.cID = NVL(Home.cId,NVL(Cell.cId,Office.cId)); ORDER BY 1 , 4don't forget to add optimization index, otherwise ...