Question 299718 in Mysql Question posted in
The official MySQL documentation can be found here.

Is there a way to combine these two MySQL queries into a single statement?

I’m trying to do some dynamic variable calculations on a select and reference those variables in the where statement. After searching the following is as close as I’ve been able to get thanks to Jonas’s post here MySQL user-defined variable in WHERE clause

I’m thinking somethere where the output is address (from the original table, @bitcoin, and @cash_value, instead of using two separate select statements as shown below.

select @address := address, @bitcoin := (value / power(10, 8)), @cash_value := @bitcoin * 43913.7515932587 FROM knownaddresses_bitcoin WHERE address = '111111111111111111112czxoHN';
select @address, @bitcoin, @cash_value where @cash_value > 1;
Tags:

2

Answers


  1. 0

    You’re on the right track with user-defined variables in MySQL. However, MySQL doesn’t allow using aliases for user-defined variables directly in the WHERE clause. You can work around this limitation by using a subquery or a derived table. Here’s an example using a derived table:

    SELECT address, bitcoin, cash_value
FROM (
    SELECT 
        address,
        value / POWER(10, 8) AS bitcoin,
        (value / POWER(10, 8)) * 43913.7515932587 AS cash_value
    FROM knownaddresses_bitcoin
    WHERE address = '111111111111111111112czxoHN'
) AS derived_table
WHERE cash_value > 1;

    This query uses a subquery to calculate bitcoin and cash_value based on the value column from knownaddresses_bitcoin for the specified address. Then, the outer query filters the results based on the cash_value being greater than 1.

    Remember to replace ‘111111111111111111112czxoHN’ with the specific address you’re interested in querying.

    This way, you avoid using user-defined variables and achieve the desired filtering based on the calculated values in a single SQL statement.

    Login or Signup to reply.
  2. 0
    1. You cannot refer on the output column’s alias in the expression for another output column.
    2. You may test the value obtained in the output column in HAVING clause (but you cannot test the variable value which is assigned in this expression).
    3. If the output list contains an expression whose value is equal to something in WHERE condition then use not a column but according literal value in the output – this is more logical.

    Finally:

    SELECT @address := '111111111111111111112czxoHN' AS address, 
       @bitcoin := value / POWER(10, 8) AS bitcoin, 
       @cash_value := value / POWER(10, 8) * 43913.7515932587 AS cash_value
FROM knownaddresses_bitcoin 
WHERE address = '111111111111111111112czxoHN'
HAVING cash_value > 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search