# finding with related records

**URL:** <https://forum.servoy.com/t/finding-with-related-records/49>\
**Category:** Classic Servoy\
**Created:** [May 15, 2003, 4:39am UTC](https://forum.servoy.com/t/finding-with-related-records/49 "2003-05-15T04:39:00Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Neale](https://avatars.discourse-cdn.com/v4/letter/n/7ab992/32.png) [@Neale](https://forum.servoy.com/u/Neale)\
**Post date:** [May 15, 2003, 4:39am UTC](https://forum.servoy.com/t/finding-with-related-records/49/1 "2003-05-15T04:39:00Z")

</div>

Either we are doing something wrong, or we’ve found a bug in finding…

If we construct a form with fields from the table the form is based on  
(“MASTER”) and also fields from a related table (“OTHER”) then we can,  
with apparently correct results, find records based on fields in the  
MASTER or OTHER records. In this case, each MASTER record can have zero  
or one related OTHER records.

We can also, again apparently correctly, logically OR two search requests  
using criteria matching fields in the MASTER (Find; enter criteria; Add  
Record; enter next criteria; … Search). This also seems OK in a method.

So far, so good - but… if we then try to OR two sets of search criteria  
where the first one specifies only fields in the MASTER (which by itself  
would return some MASTER records which do not have related OTHER records)  
and the second specifies field(s) in the OTHER record we then find that  
the foundset excludes MASTER records which match the first set of criteria  
but which do NOT hve a related OTHER record. I.e. the foundset of the  
first request in the search appears to be influenced by referencing fields  
in a related record in the second request of the find.

Are we doing something wrong, or have we tripped over a bug?

FWIW, this has been observed with Servoy 1.01 on two different platforms:

- Mac OS-X, Servoy 1.01, Java 1.4.1, MySQL
- Linux, Servoy 1.01, Java 1.3.1, PostgreSQL

Thanks,  
Neale.

---

<div class="post-metadata">

**Author:** ![maarten](https://avatars.discourse-cdn.com/v4/letter/m/ecccb3/32.png) [@maarten](https://forum.servoy.com/u/maarten)\
**Post date:** [May 15, 2003, 7:33am UTC](https://forum.servoy.com/t/finding-with-related-records/49/2 "2003-05-15T07:33:59Z")

</div>

Hi Neale,

This is a known issue. The search engine automatically joins a related table when you have a related column in your form. So if you indeed do a search on a record that doesn’t have related data, the outcome is empty. We deliberately don’t use outer joins, because those are expensive queries. Currently we are looking for the best way to solve this, keeping performance in mind.

Thanks for your input.

---

<div class="post-metadata">

**Author:** ![Neale](https://avatars.discourse-cdn.com/v4/letter/n/7ab992/32.png) [@Neale](https://forum.servoy.com/u/Neale)\
**Post date:** [May 15, 2003, 8:48am UTC](https://forum.servoy.com/t/finding-with-related-records/49/3 "2003-05-15T08:48:08Z")

</div>

Hmm… not sure if we are talking about the same thing here.

I don’t think outer joins (or lack thereof) come into my issue - except when joining the multiple parts of a search request.

As I said, we start with a form with fields from it’s base table plus fields from a related table. I can do a search on fields in the base table just fine, regardless of if the matching records have related records. I can search on fields in the base table plus fields from the related table (this obviously being an inner join of the two tables.

But if I attempt to combine the above two, then the first request , surprisingly, appears to become an inner join with all records in the related table (even though that part of the search specification doesn’t reference any fields in the related table).

'tis quite odd indeed.

Thanks,  
Neale.