Multiple-Row SubQuery

[marabou]

I have developed a problem using SQL Plus. 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

 

[MikeJones]

Search through the forum. There are lots of posts on this subject.

 

[marabou]

Thanks for the advice, I already did, but as I am pretty new to Oracle and SQL, I'm not really too sure what I'm looking for. I was hoping someone would give me a hint.

Thanks anyway

 

[MikeJones]

OK I tried searching and got nothing back, apart form your original posting so...


I'm not completely sure what your expecting in terms of out put but try the following...

Select Customers.customerID custid,
LicenceID,
Contacts.CustomerID contactid
FROM Customers, contacts, Licences
Where Customers.customerID = Licences.customerID
and Customers.customerID = Contacts.customerID (+)

I'm not sure why you have the subquery, form what I can see you just need to join the three tables to get back all the info. Customers is the master driving table and for each row in customers that you want returned you will be garenteed a row in licences but not always a row in contacts. you may also have more than 1 row in contacts for each customer.

 

[Guest_imported]

I created a view in PL/SQL that joins data from many different tables and when a simple select statement is done, it works fine, but as soon as I try to do an ORDER BY on the query, I get ORA-01427

SELECT
proj_id
, proj_nm
, proj_nm_abbr
, sts_cd
*fields removed*
, termination_reason_txt
, termination_dt

FROM
mts_proj_queryTool

order by
proj_id

Without the last two lines, the code executes fine. What's up?