Multiple-Row Subqueries

[marabou]

I have developed a problem using SQL Plus and Oracle 8i. I keep getting the error
ORA-01427: single-row subquery returns more than one row

I have Customers who have an id, Contacts, which are linked using the foreign key CustomerID and Licences which are linked using the foreign key CustomerID.
There are customers, who have may have none, one or many licences and contacts.

So I need to work out how to do a multiple-row subquery, as the query is producing the right results, but stops as soon as it gets to where there is more than one contact in the result set it errors.

[Select Customers.customerID, LicenceID,
(Select Contacts.CustomerID
FROM Contacts, Customers
Where Contacts.customerID (+)= Customers.customerID
FROM Customers, Licences
Where Customers.customerID = Licences.customerID]

Please help, I can't find any references on how to do multiple-row subqueries anywhere.

Thanks in advance

 

[MalcolmW]

I don't think there is a way to do that, but as with most things in SQL, there is a way around the problem. I'm not an Oracle person, so the syntax should be taken with scepticism.

SELECT Customers.customerID
, LicenceID
, DT.CustID
FROM Customers
INNER JOIN Licences ON Customers.customerID = Licences.customerID
OUTER JOIN
(SELECT Contacts.CustomerID CustID
FROM Contacts) DT ON DT.CustID = Customers.customerID

Malcolm

 

[stoleru]

Could you describe more clearly your problem?
I undestood that you want a query which should output rows like:
CustomerID1,LicenceID1,ContactID1
CustomerID1,LicenceID1,ContactID2
CustomerID2,NULL,ContactID3
CustomerID2,NULL,ContactID4
CustomerID3,NULL,NULL
CustomerID4,LicenceID2,ContactID5
CustomerID4,LicenceID2,ContactID6
CustomerID4,LicenceID3,ContactID5
CustomerID4,LicenceID3,ContactID6
.........................

I.e. matching all licence and contact informations for every specific Customer?

If is it true, then this Oracle solution should work:

SELECT customers.customerID,licences.licenceID,contacts.contactID
FROM customers,licences,contacts
WHERE customers.customerID=licences.customerID(+) AND
customers.customerID=contacts.customerID(+);

Eduard Stoleru
e_stoleru@yahoo.com

 

[marabou]

Thanks v. much, it worked!