I have the following SQL statement:SELECT DISTINCT OD.PO_ID, OD.LINE_NUMBER, OD.PART_ID, OD.PO_DESC, OD.REV_DEL_DATE, OD.REV_ORDER_QTY, RT.QUANTITY, OD.PO_LINE_STATUS, VM.VENDOR_NAMEFROM ((ESIDB.dbo.POFOD OD RIGHT OUTER JOIN ESIDB.dbo.POFRT RT ON (OD.PO_ID=RT.PO_ID) AND (OD.LINE_NUMBER=RT.LINE_NUMBER))INNER JOIN ESIDB.dbo.POFOM OM ON RT.PO_ID=OM.PO_ID) INNER JOIN ESIDB.dbo.POFVM VM ON OM.VENDOR_ID=VM.VENDOR_IDWHERE OD.REV_DEL_DATE < GETDATE() AND OD.PO_LINE_STATUS = 'O'
The problem is that I also need to get All open POs whose REV_DEL_DATE < GETDATE() when there is no POFRT record. Right now, I only get those records when there is a POFRT record. I would like to get it when there is not one. The only thing I can think of is to do a union with a similar statement. However, I am hoping to not do that as it duplicates records. I also need to put this into a Crystal Report and an Alert. I don't know if both products will use the UNION without a lot of complication.In case you are wondering, the POFOM is only used to link to a record in the POFVM table, that is why I don't select any fields from that table. There is no direct link between POFVM and the other two tables.I am not a great SQL programmer and know that some of you will have a slick way of doing this. 
Any help would be greatly appreciated.I am getting closer. Here is what I have now.SELECT DISTINCT OD.PO_ID, OD.LINE_NUMBER, OD.PART_ID, OD.PO_DESC, OD.REV_DEL_DATE, OD.REV_ORDER_QTY, RT.QUANTITY, OD.PO_LINE_STATUS, VM.VENDOR_NAMEFROM (ESIDB.dbo.POFOD OD RIGHT OUTER JOIN ESIDB.dbo.POFRT RT ON (OD.PO_ID=RT.PO_ID) AND (OD.LINE_NUMBER=RT.LINE_NUMBER)or (OD.PO_ID <> RT.PO_ID))INNER JOIN ESIDB.dbo.POFOM OM ON OD.PO_ID=OM.PO_ID INNER JOIN ESIDB.dbo.POFVM VM ON OM.VENDOR_ID=VM.VENDOR_IDWHERE OD.REV_DEL_DATE < GETDATE() AND OD.PO_LINE_STATUS = 'O'
But I get too many duplicate - erroneous records. I get 3050 records when I should only have about 12.
Help with SQL statement
Posted - 2011-05-06 : 13:49:08
