Search criteria in inner join and display ad distinct

Applications Grid / Reports
#1

Hi, I’m new in scriptcase.

I have two table:
TABLE A
IDcustomer
name

TABLE B
IDCustomer
properties

So a customer can have more properties.

If I use the
SQL: SELECT IDcustomer, name FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.IDcustomer = TABLE_B.IDcustomer)
and I search a customer with the property “aaa”, I get the result:
1 John
1 John

So “John” is shown twice, because he has 2 properties (i.e. “aaa” and “bbb”)

It’s possible to show distinct rows in the grid result ?

Thanks :wink:

#2

Re: Search criteria in inner join and display ad distinct

If you only need id and name in the grid, which are both in table A, why join table B in the query?

#3

Re: Search criteria in inner join and display ad distinct

To do a search in table B :wink:
Otherways how can I search a customer’s property (field in table B) and displaying only distinct value of table A ?

#4

Re: Search criteria in inner join and display ad distinct

As far as I know, this cannot be achieved by means of standard search fields.
Also a “search application” does not offer an out-of-the-box solution (cannot be linked to grid).

What might be a workaround is using a control application. Here’s an example:

table orders:
id

table order_lines:
order_id
product_id

You want to search on product_id, and a grid must display all orders (once) containing this product.

Grid application:

  • SQL: select * from orders o
  • event “OnScriptInit”: sc_select_where(add) = "where exists ( select 1 from order_lines where order_id = o.id and product_id = [pid] ) ";

Control application:

  • 1 field “product”
  • link of type application, to Grid application, parameter: pid = (field) product

The product must match the id exactly. If you want “contains” search options well, or you want multiple parameters to search on, then you have to do some more programming. Good luck.

#5

Re: Search criteria in inner join and display ad distinct

Try:

SELECT IDcustomer, name FROM table_a AS a
LEFT JOIN table_b AS b on a.IDcustomer = b.IDcustomer
GROUP BY a.IDcustomer, name

#6

Re: Search criteria in inner join and display ad distinct

Maybe I don’t understand it right, but when you want to search on field “property”, you have to include field property in the select don’t you?