At 01:15 AM 03-01-01 +0100, you wrote:

>Your query works, thanks. But it doesn't solve my problem, even with explicit
>JOINs only the current row displays the correct data.

Oh, I just noticed the subselect clause, where each subselect is applying
the SAME alias to the subselected column. This led me to rethink your
query. Of course, what you are looking to do here needs to be done with a
UNION query because all of the columns in your dataset have 3-way
ambiguity. This, after all, would be the main reason for your problem.

As the statement stands, the query logically should return one column for
each one selected, i.e. a total of 18 columns. I suspect this isn't what
you are after, anyway. If it *is* what you want, then apply an alias to
each column in 'B' and 'C' to remove the ambiguities.

Sorry for not noticing this before....

Helen

>Helen Borrie wrote:
>
> > At 08:23 PM 02-01-01 +0100, you wrote:
> > >I have a IB_Query with the following SQL-Statement:
> > >select
> > >FD.FDID,FD.FDDCID,FD.FDDFID,FD.FDBONUS,
> > >A.FMID,A.FMFDID,A.FMMPID,(select MPNAME from MAINPRODUCTGROUPS where
> > >MPID=A.FMMPID) as MPNAME,A.FMDISCOUNT,A.FMDISCOUNTNEW
> > >,
> > >B.FMID,B.FMFDID,B.FMMPID,(select MPNAME from MAINPRODUCTGROUPS where
> > >MPID=B.FMMPID) as MPNAME,B.FMDISCOUNT,B.FMDISCOUNTNEW
> > >,
> > >C.FMID,C.FMFDID,C.FMMPID,(select MPNAME from MAINPRODUCTGROUPS where
> > >MPID=C.FMMPID) as MPNAME,C.FMDISCOUNT,C.FMDISCOUNTNEW
> >
> > from FOLLOWUPDISCOUNTS FD
> >
> > join FOLLOWUPDISCOUNTS_MP A
> > on A.FMFDID=FD.FDID
> >
> > join FOLLOWUPDISCOUNTS_MP B
> > on B.FMFDID=FD.FDID
> >
> > join FOLLOWUPDISCOUNTS_MP C
> > on C.FMFDID=FD.FDID
> >
> > where A.FMMPID=11 and B.FMMPID=12 and C.FMMPID=13
> >
> > btw, the joinlinks you are using won't work because you need the actual
> > table identifier, not an alias. Thus I believe that you have no choice but
> > to use explicit join syntax for a re-entrant query like this.
> >
> > Cheers,
> > Helen
> >
> > All for Open and Open for All
> > InterBase Developer Initiative · http://www.interbase2000.org
> > _______________________________________________________
>
>

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________