--- In IBObjects@yahoogroups.com, Guido Klapperich
<guido.klapperich@t...> wrote:

>
> > Try this:
> >
> > select
> > pr.PCID,
> > pr.PCPAID,
> > pr.PCMPID,
> > pr.PCNR,
> > pr.PCNAME1,
> > (select pa.PANAME from PRODUCTAREAS pa
> > where pa.PAID=pr.PCPAID) as PANAME,
> > (select mpg.MPNAME from MAINPRODUCTGROUPS mpg
> > where mpg.MPID=pr.PCMPID) as MPNAME,
> > pr.PCTARGETRELEASEDATE,
> > pr.PCLISTPRICE,
> > pr.PCRRP,
> > pr.PCDAYONE,
> > pr.PCMINORDERQUANTITY
> > from PRODUCTS pr
> > join ORDERITEMSTABS_MP oit
> > on pr.PCMPID=oit.OMMPID
> > where
> > oit.OMOAID=3
> > and pr.PCACTIVE=1
> >
> > For KeyLinks: PCID=ORPCID
>
> I have tried it, but I still get the exception.
>
> > First, you might still get a multiple row exception, since there

is nothing

> > to stop this query from returning multiple rows.
>
> That's not right. If IBO would use my complete where-part "where
> OMOAID=3", it would get only one row per fetch. The where-clause

makes

> the query unambigous.
>
> > The second is that you seem to be trying to use a lookup

relationship to

> > implement an "after-the-event" master-detail relationship within

the

> > logical structure of your tables. There is a risk here of

putting the

> > dependencies inside the lookup set out of scope.
>
> Sorry, but why do you think, I'm doing that?
>
> Regards
>
> Guido
>

Whenever I get the "multiple rows in a singleton select" in a query
like this the first thing I do is check the subqueries. You have two.
I would suggest trying to "max" them and see if you still get the
exception.

(select MAX(pa.PANAME) from PRODUCTAREAS pa
where pa.PAID=pr.PCPAID) as PANAME,
(select MAX(mpg.MPNAME) from MAINPRODUCTGROUPS mpg
where mpg.MPID=pr.PCMPID) as MPNAME,


Stan