topic Re: Select rows in one table with values identical in another table in Discussions

Select rows in one table with values identical in another table

Neo — Sat, 10 Jun 2023 23:53:19 GMT

I have two tables: tableA and tableB.

Rows in one column (say col-1) in tableB has values also occurring in tableA in a column (say col-15). Length or col-15 >> col-1.

Can I interactively select the rows in col-15 (of tableA) with entries also occurring in col-1 (of table-B)?

If interactive selection is not possible, how to script such a selection process?

Thanks.

Re: Select rows in one table with values identical in another table

jthi — Fri, 19 Aug 2022 12:27:03 GMT

I think you will have to join the tables or use query builder if you want to do this interactively. Scripting will have many different options depending on how rows are selected and what is being selected.

Re: Select rows in one table with values identical in another table

Ressel — Fri, 19 Aug 2022 12:41:31 GMT

Here's what I do to a) get all nominal values in a given column of one table and b) use these values to make a selection in a different table.

yourSelection = Associative array( tableB:"col-1" ) << Get Keys; // gets an array of the values in col-1 in tableB

tableA << Select Where( Contains( yourSelection, :"col-15" )); // should select all rows with identical values in col-15 in tableA

Re: Select rows in one table with values identical in another table

Georg — Fri, 19 Aug 2022 14:19:01 GMT

If the values are unique, you could try to virtually link tables, but the result is different.

In general (JSL) I think I would apply a row state handler, see screenshot and example. See also Scripting Index for Examples ...

You can delete the debugging part "print, show ", its only for better understanding.

Names Default To Here( 1 );

dtA = New Table( "tableA", add rows( 10 ), New Column( "col_15", "Numeric", "Ordinal", set each value( Random Integer( 10 ) ) ) );
dtB = New Table( "tableB", add rows( 10 ), New Column( "col_1", "Numeric", "Ordinal", set each value( Random Integer( 10 ) ) ) );

f = Function( {a}, 
	Show( dtA << get selected rows );
	val_lst = dtA:col_15[dtA << get selected rows];
	dtB << clear select();
	For Each( {value}, val_lst, print(value); dtB << select where( :col_1 == value, current selection( "extend" ) ) );
);

rs = dtA << make row state handler( f );