Help with strange query 1

[CraigBest]

Hope someone can help me.

I have to construct a query that will do the following things:

* Join two tables as follows:

"...WHERE PO.PONUM = LI.PONUM (+) AND..."

This part works, I know.

Here's the strange part: There can be more than one record in LI for each PO, and there can also be no record in LI for a PO. I want to return only the first record in LI if it exists, so I included the following:

"...AND LI.LineItemNum = '1'..."

This has the good effect of only returning the records if there are at least one LI records for a join, but if there are no LI records for a PO record I don't get the PO record data like I want.

How can I fix this query to get back All PO records, plus the corresponding first LI record if available?

Thanks

Current query below...

SELECT * FROM PO P, LineItem LI WHERE P.PONum = LI.PONum (+) AND LI.LineItemNum = '1';

Craig in NJ

 

[lewisp]

SELECT *
FROM PO P,LineItem LI
WHERE P.PONum = LI.PONum(+)
AND LI.LineItemNum(+) = '1';

 

[CraigBest]

Woo-hoo! That did it, thanks. Just what the doctor ordered. I've never seen a reference like that in any of the books I've read.