Search in 2 tables concomitant

[fciprian]

Hello.
I have 2 tables of clients: Clients A and Clients B
In the structure i have Name and Status (Active or Inactive)

In a new table, How can i search in BOTH tabels for clients with ACTIVE status?
I have only setted up for one table seach:
SELECT `Clients.A`.`id`, `Clients.A`.`Name` FROM `Clients.A` WHERE `Status` = 'Active' ORDER BY 2

But is there a posibility to set custom sql to search in BOTH tables (Clients A and Clients B) for users with status Active?

Thank you in advance!

[pbottcher]

Hi,

you may try the UNION.

Code: Select all

SELECT `Clients.A`.`id`, `Clients.A`.`Name` FROM `Clients.A` WHERE `Status` = 'Active' 
UNION
SELECT `Clients.B`.`id`, `Clients.B`.`Name` FROM `Clients.B` WHERE `Status` = 'Active' 

[fciprian]

Thank you. This was a verry good idea.
Now i have another isue:
If i select a client with id 1 from Clients B table, it is showing the name of client with id 1 in Clients A table.

How can i solve this? in app gini i select in lookup field the parent table Clinets A and parent caption field Name

Thank you in advance!!!

[pbottcher]

Hi,

assuming that you show the result of the SQL query in the TV of a table.

You will need to have an additional information that can be used to differentiate from which table the id is.

If you can be sure that you will never have more than x (e.g. 1mio) amout of client in table A, you could add 1 mio to the id of the result of table 5.

Next you will need to use JS to check in the TV for the references that are above 1 mio and change the href statement to point to table b with the correct id's (= -1mio).

Hope that gives you a starting point.