Type of Join to use or instead procedure

[MatthewYoung]

Quickly described I have a Web client that has a search page where users apply filters (search criteria) and the absense or lack of results from one filter should not effect the others. Filters ought to be applied individually and only those returning results are intersected. Not sure if this can be done via SQL or requires program logic.

For example, say I have widgit objects with the following possible filters:
[1] widgits related to another specific widgit
[2] widgits related to another object
[3] values of a specific widgit attribute

If filter [1] returns 50 records, [2] nothing, and [3] nothing then the result set should be 50 (dvs. intersection of non-null result sets in this case just the one). If filter [1] return 50 records, [2] nothing, and [3] 2 records and only 1 records in [1]s result compaired to [3]s results matches then the final result set should be 1 records.

 

[Z3]

You can have all filter create result temp tables and only inner join those that returns data. This should be easy with some program logic.