TBGrid SQLQuery : Manual versus Automatic

Hello Dmitry and all MVD fans,

I'll try to be as clear as possible.

In one projet, I have a TableGrid in wich rows are displayed, filtered by 4 comboboxes.

All info beeing in the same table, the filtering works very well, even if some Comboboxes are empty.

As you can see here, one Combobox is empty and it still works when you click on the FILTER button.

The more Comboboxes you use, the more filtering you get, BUT you don't have to use ALL Comboboxes.

The FILTER button is configured like this

It works very well !

In another project, because infos are in multiple tables, I can not use the search function for the button, because you can only assign, one table.

So I wrote a query for the button.

Here it is :

SELECT
asset.asset_sku,
asset.asset_name,
artist.artist_name,
keyword.keyword_text,
main_cat.main_cat_name,
vendor.vendor_name
FROM
asset
INNER JOIN asset_artist ON asset_artist.id_asset = asset.id
INNER JOIN artist ON asset_artist.id_artist = artist.id
INNER JOIN asset_kw ON asset_kw.id_asset = asset.id
INNER JOIN keyword ON asset_kw.id_keyword = keyword.id
INNER JOIN main_cat ON asset.id_main_cat = main_cat.id
INNER JOIN vendor ON asset.id_vendor = vendor.id
WHERE
(artist.id = {ComboBox1}) AND
(keyword.id = {ComboBox2}) AND
(vendor.id = {ComboBox3}) AND
(main_cat.id = {ComboBox4});

This code works well, no problem except one : it only works if ALL 4 Comboboxes are selected. If one is empty, there is no result returned.

I suspect it is because of the AND clause in the query which seems logical.

My question to Dmitry : do you do something special in the search query built-in behind the button to be able to handle empty comboboxes in MVD ? In other words, what is your trick to handle the empty id returned by non used Comboboxes ? Do you have some kind of wildcard or some conditional CLAUSE like if id=NULL then select * (just guessing) ?

Have a nice day and thanks in advance

Cheers

Mathias