Omitting Multiple Records, HOW?

[Infop]

Hi All,

I have a relation based on a intermidate table. The relation fetches records which may have contain some same records multiple times. I would like to show a single record for each multiple records.

How can I do that in Servoy?

Thanks,

[ROCLASI]

Hi Infop,

Can you tell us what you are doing exactly.
Are you using SQL or not? Are you using the controller or datasets? Are you sorting on related data? What Servoy version do you use ?
etc.

[Infop]

Hi Robert,

I have a table which is working as an intermidiate table to establish M:N relationship, Costomer To Product. A Customer can be related to number of Products and a Product Can be related to number of Costomers. I have relation which will filter out the logged in costomer's product list. As The relation is based on the intermidate table, It is showing list of records for the same product for multiple times. I would like to list only the distinct product list.

So, I would like so to distinct products of the logged in user.

For an example,

Let's say User "P" is related to product-1, product-2 and product-3 and the Intermidiate table has the records.

P - product-1
P - product-1
P - product-1
P - product-2
P - product-2
P - product-3

So, I would like to show only distint records (omitting repeated), such as

P - product-1
P - product-2
P - product-3

Please, specify a way whether based on SQL/Controller.

My Servoy Version.
Version: 4.1.3 - build 672

Many Thanks.

[ROCLASI]

Hi Infop,

I think you should show the data from the perspective of the products table (not the intermedidiate table) like so:

Code: Select all

// Get all distinct PK's from the products table that are linked to the customer
var _sQuery = "SELECT DISTINCT products.pkColumn \
     FROM products JOIN customerProduct ON products.pkColumn = customerProduct.fkColumn \
     WHERE customerProduct.fkCustomerColumn=?";

// Now load the distict product PK's using the controller.
controller.loadRecords(_sQuery, [_nCustomerID]);

Of course you need to change the table/column names accordingly....
And when you know the customer PK already then you don't need to join also to the customer table. If you are filtering my any other data than the customer PK then you need to add a join to the customer table as well.

Hope this helps.

[Infop]

Thanks a LOT, Robert.

It Works.

Great.

Thanks a lot again.

[Infop]

Does this work for a datetime field?

Code: Select all

// Get all distinct PK's from the products table that are linked to the customer
var _sQuery = "SELECT DISTINCT products.pkColumn \
     FROM products JOIN customerProduct ON products.pkColumn = customerProduct.fkColumn \
     WHERE customerProduct.fkCustomerColumn=? AND customerProduct.launchDate<=?";

// Now load the distict product PK's using the controller.
controller.loadRecords(_sQuery, [_nCustomerID, _dProductLaunchDate]);


The above code snippet shows the same. But, It is not working. The field, _dProductLaunchDate is a DateTime Variable.

Thanks,

[ROCLASI]

Does this work for a datetime field?

Yes, it should.

Code: Select all

// Get all distinct PK's from the products table that are linked to the customer
var _sQuery = "SELECT DISTINCT products.pkColumn \
     FROM products JOIN customerProduct ON products.pkColumn = customerProduct.fkColumn \
     WHERE customerProduct.fkCustomerColumn=? AND customerProduct.launchDate<=?";

// Now load the distict product PK's using the controller.
controller.loadRecords(_sQuery, [_nCustomerID, _dProductLaunchDate]);


The above code snippet shows the same. But, It is not working. The field, _dProductLaunchDate is a DateTime Variable.

What datatype is the column in the database ? Also what database do you use ? Sybase ?