John, Change all your joins on the Suppliers and Locations tables to be "left Joins" not "inner joins".
Dave Crozier -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of John Weller Sent: 22 January 2008 22:22 To: [email protected] Subject: SQL Problem Hi Guys, I have a problem that is driving me nuts! I have 2 tables holding details of stock movements, referred to as Transactions, TransHeader and TransDetails. TransHeader holds details of the transaction type, TransType, which can be Goods In (GI), Transfer (TF) or Goods Out (GO). There are 2 tables, Suppliers and Locations each with an integer ID field and a name. TransHeader has 4 integer fields to hold FromSuppID, FromLocnID, ToLocnID and ToSuppID. If the TransType is GI the FromSuppID and ToLocnID are populated with values and the others are zeroes, if it is TF the SuppID fields are zero with values in the others, etc. I need to populate a cursor with details of TransDate, TransType, FromSuppName, FromLocnName, ToLocnName, ToSuppName for each record in TransDetails where the stockID has a particular value. I have used the View generator to create an SQL statement which I have modified by adding conditionals to test for zeroes as shown below: SELECT Transheader.transdate, Transheader.transtype,; IIF(EMPTY(Transheader.fromsuppid), SPACE(50), Suppliers.suppname) as FromSuppName, ; IIF(EMPTY(Transheader.fromlocnid), SPACE(50), Locations.locnname) as FromLocnName, ; IIF(EMPTY(Transheader.tolocnid), SPACE(50), Locations_a.locnname) as ToLocnName, ; IIF(EMPTY(Transheader.tosuppid), SPACE(50), Suppliers_a.suppname) as ToSuppName, ; Transdetails.qty; FROM ; kactstock!transheader ; INNER JOIN kactstock!transdetails ; ON Transheader.transid = Transdetails.transid ; INNER JOIN kactstock!suppliers Suppliers_a ; ON Transheader.tosuppid = Suppliers_a.suppid ; INNER JOIN kactstock!locations Locations_a ; ON Transheader.tolocnid = Locations_a.locnid ; INNER JOIN kactstock!locations ; ON Transheader.fromlocnid = Locations.locnid ; INNER JOIN kactstock!suppliers ; ON Transheader.fromsuppid = Suppliers.suppid ; ORDER BY TransHeader.Transdate descending ; WHERE transdetails.stockid = 578 It works perfectly if all of the ID fields are positive but if any are zero there is no result despite the IIF statements. I'm sure it is something very simple that I cannot see but I'm baffled. Help!!! TIA John Weller 01380 723235 07976 393631 [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
