Is it possible to include computations in where clause of mysql?

[Winston Liek]

I'm not sure if is this possible using a single MYSQL statement in JDBC. This is a sample:

Table 1:
ID NO | NAME | MULTIPLIER | ALLOWED_MIN_MONEY
1 | MAI SAM | 2 | 60
2 | GEORGE SMITH | 5 | 70

Table 2:
ID NO | NAME | MONEY
1 | MAI SAM | 25
2 | GEORGE SMITH | 18

What I am trying to accomplish is retrieve only the records where tbl1.ALLOWED_MIN_MONEY > (tbl1.MULTIPLIER * tbl2.MONEY).
But I can't put it directly on the where clause for filtering the results.

This is the result I'm looking for:
Table Result:
ID NO | COMPUTED MONEY | ALLOWED_MIN_MONEY | DIFFERENCE
1 | 50 | 60

From the result, only id 1 is retrieved since it satisfies the condition: 60 > (25*2)

I was also thinking maybe I can try to use multiple subselect with multiple inner join. But I'm not sure if it is efficient as I will process more than thousand rows..

Can someone please advice me. TIA

[Martin Vashko]

Why can't you put it in the WHERE clause?

To query contents of two tables, you have to "join" them. There's an SQL clause for that (see the documentation).

You also need to decide which columns to join the table on - these are the columns that identify rows in both tables that correspond to each other, so to say. However, it looks like the structure of your tables isn't normalized - for example, the "name" field is repeated in both tables, which generally makes administering the data difficult (if someone changes his name, you need to change it on several places in your database to reflect the change, and it is "not good" - having some attribute on more than one place is one of the ways in which a database structure can be "not normalized".

[Roel De Nijs]

Winston Liek wrote:I was also thinking maybe I can try to use multiple subselect with multiple inner join. But I'm not sure if it is efficient as I will process more than thousand rows..

Once again, 1000 rows for a database is really nothing.

But the query you are looking for is very straightforward and easy, so you don't need subselects. All you need is an inner join of table1 and table2 (I assume on ID) and then add the condition from your OP to the where clause and you'll be finished.

So to answer your question: yes, it's possible to include computations in the where clause of a select statement