SQL Select help.

Aleniko

Hi;

I'm using this command:

SELECT polgr.pono,po.podate as podate,polgr.design,polgr.qopen,IIF(isnull(poarrivals.design)," ","Y") ;
FROM;
krcbp!polgr ;
LEFT JOIN krcbp!po ;
ON polgr.pono = po.pono ;
LEFT JOIN poarrivals ;
ON polgr.pono = poarrivals.pono ;
WHERE polgr.design = "SU-115" AND polgr.qopen <> 0;
ORDER BY podate

But I'm trying to get only the FIRST result from the poarrivals table. I need to show "Y" if there are any entries in the poarrivals table. Instead, I'm getting multiple results (Because there are multiple entries in the poarrivals table)

I tried to use group by but I was getting errors.

Can someone give me the right syntax

Thanks.

Vladimir Zografski

You could try

Code Snippet

SELECT polgr.pono,po.podate as podate,polgr.design,polgr.qopen,"Y" AS yesNo;

FROM;

krcbp!polgr ;

LEFT JOIN krcbp!po ;

ON polgr.pono = po.pono ;

INNER JOIN (SELECT pono FROM poarrivals GROUP BY Pono) AS T ;

ON polgr.pono = T.pono ;

WHERE polgr.design = "SU-115" AND polgr.qopen <> 0;

ORDER BY podate

hangover

or alternatively ...

Code Snippet

SELECT polgr.pono,po.podate as podate,polgr.design,polgr.qopen,MAX (IIF(isnull(poarrivals.design)," ","Y")) ;

FROM;

krcbp!polgr ;

LEFT JOIN krcbp!po ;

ON polgr.pono = po.pono ;

LEFT JOIN poarrivals ;

ON polgr.pono = poarrivals.pono ;

WHERE polgr.design = "SU-115" AND polgr.qopen <> 0;

GROUP BY 2, 1, 3, 4

(but GROUP BY will not work if any of the fields being grouped are Memo, General, or Blob fields)

Aleniko

Thank you both.

SQL Select help.

Re: SQL Select help.

Re: SQL Select help.

Re: SQL Select help.