SQL - How to return blank records

[SimonBray]

I am new to using R&R SQL version 9 (have used XBase version for 12 years) so forgive my ignorance if my question is trivial or nonsense. I am trying to relate a master table to a secondary table and if I do not find any records in the secondary table I still want the master record to be included in my data set. In Xbase R&R this was the default action. The problem I seem to have with SQL is that in order to relate to the secondary table I am having to specify an outer join (I am looking for records in the secondary table with a matching order number but also with a certain criteria on another of the fields in the secondary table - so my join is where master.orderno=secodnary.orderno and where master.criteria=secondary.criteria). This does not seem to permit the equivalent of returning "Blank" if no match found.____Am I making any sense to anyone ?____Regards,__Simon.

[kfleming]

For the non-matching records, all of the fields in the related table will be empty. So if you are filtering on conditions where a master field has to be equal to a related field, those blanks will disappear.__You really need to OR a condition where MASTER.linkfield is not equal to RELATED.linkfield since that will be true for all of the blank related records.____Kathleen__R&R Support

[Simon_Bray_(Guest)]

I am not sure that I have made myself clear enough. My problem is that I believe that R&R SQL behaves differently than R&R Xbase when you attempt to relate to a table on more than one field condition. Whereas in Xbase if no matches are found in the related table you will still get your master record, in SQL it appears that if not match is found in the related table then you don^t get the master record either. ____My example is a master table of orders and a related table of order costs. If an order has a particular type of costs I want to show the value, if the order does not have that cost then I still want to show the order but with a zero value for this cost. ____So I am joing the two tables where table1.orderno=table2.orderno and calculation costname=table2.costname and eturn blank if not found.____When I do this in XBase it worls. In SQL I only get those orders in table 1 that have a record in table 2.____Simon. ____

[kfleming]

The default behavior for a SQL join is to only include records having a match. (The equivalent of the Xbase skip failure action) In order to see a master record for which there is no related match, you need to edit the relation, click the Options button and select Include unmatched records on Left (the equivalent of the Xbase blank failure action.)__Is that how things are set in your report?____Kathleen__R&R Support

[Simon_Bray_(Guest)]

You can not click that option when you have an outer join.____Simon.

[kfleming]

The availabilty of join options depends on the ODBC driver. Which one are you using?____Kathleen__R&R Support

[Simon_Bray_(Guest)]

I am running Windows 2000 SP2 SRP1. The ODBC parts all seem to show as version 3.520.6526.0____Regards,__Simon Bray.____

[Simon_Bray_(Guest)]

I has downloaded and installed MDAC 2.7 which upgardes the ODBC compoments to 3.530.9001.0 but I now realise that the issue is not that the option to include blank records is disbaled but that if you select that option when you have an oter join you get the follwing error message :-____"Both items of an oter join must contain columns"__"Incorrect syntax near keyword .and."__"Statements could not be prepared"____Simon.