Return 1 row from joined table

From: Mike Dole <m_doolio_at_hotmail.com>
Date: 23 Dec 2004 01:03:21 -0800
Message-ID: <fddbbab7.0412230103.4615709@posting.google.com>

I'm having a problem with this SQL string, I want 1 record from each joined salestrans table like for instance:

SALESNUMBE ITEMNUMBER
---------- --------------------

    738744 6398000
    738734 1201270
    738737 2216000
    738644 5251000
    738694 5943000
    737714 2401000
    736774 5943000

But the following SQL string:

SELECT str.salesnumber, str.itemnumber FROM XAL_SUPERVISOR.SALESTRANS STR, XAL_SUPERVISOR.SALESTABLE ST WHERE str.dataset = 'FIK' AND st.dataset = 'FIK' AND st.salesnumber = str.salesnumber AND st.DELIVERYDATE = TO_DATE('23-12-2004', 'DD/MM/YYYY') AND st.FIKRetbon = 1 AND ROWNUM = 1 Returns only:

SALESNUMBE ITEMNUMBER
---------- --------------------

738744 6398000

Can someone please tell me how I solve this?

Thanks in advance,

Mike Received on Thu Dec 23 2004 - 03:03:21 CST