Select query within select query

Question

    SELECT
  merchant.merchantname 'MerchantName',
  COUNT(transaction.transactionid) 'NoofTransaction',
  SUM(transactionamount) 'TotalAmount'
    (SELECT statement.statementbalance from statement where transactionid = statement.transactionid ORDER BY merchantid LIMIT 1) AS 'BalanceRemaining'
  FROM transaction
  RIGHT JOIN merchant ON transaction.merchantid = merchant.merchantid
    RIGHT JOIN statement ON transaction.transactionid = statement.transactionid
  WHERE transaction.transactiondate = '2018-01-15'
  GROUP BY merchant.merchantid;
    ORDER BY merchant.merchantid ASC;

Im trying to select a query within a select query which allows me to select the last inserted result of Balance Remaining.

which table does "Balance Remaining" come from? Is it statement? What is the actual column name? what is/are the column/s that determine "last inserted" result? — Paul Maxwell
– Paul Maxwell, Commented Jan 16, 2018 at 2:31

Paul Maxwell · Accepted Answer · 2018-01-16 03:25:54Z

1

I imagine the following revised query for your requirement which does not join to the statement table as such a join will probably multiply the number of rows and therefore produce errors in the results.

SELECT
      merchant.merchantname  'MerchantName'
    , COUNT(t.transactionid) 'NoofTransaction'
    , SUM(t.transactionamount) 'TotalAmount'
    , (
            SELECT
                  statement.statementbalance
            FROM statement
            WHERE t.transactionid = statement.transactionid
            ORDER BY
                  some_date_column DESC LIMIT 1 
      )                      
      AS 'BalanceRemaining'
FROM merchant
INNER JOIN `transaction` t ON merchant.merchantid = t.merchantid
WHERE t.transactiondate = '2018-01-15'
GROUP BY
      merchant.merchantid
ORDER BY
      merchant.merchantid ASC
;

NOTE, that the date column(s) to use for ordering ("last inserted") isn't given in the question.

Also I have reversed the table relationship. There isn't much point in using right joins in my view as it is almost always easier to structure the query to use the "from table" so that subsequent joins never need a right join. In this particular case an outer join of any form does not appear to be needed due to the where clause, so an inner join is used instead.

added: To list all merchants:

SELECT
      merchant.merchantname  'MerchantName'
    , COUNT(t.transactionid) 'NoofTransaction'
    , SUM(t.transactionamount) 'TotalAmount'
    , (
            SELECT
                  statement.statementbalance
            FROM statement
            WHERE t.transactionid = statement.transactionid
            ORDER BY
                  some_date_column DESC LIMIT 1 
      )                      
      AS 'BalanceRemaining'
FROM merchant
LEFT JOIN `transaction` t ON merchant.merchantid = t.merchantid
                         AND t.transactiondate = '2018-01-15'
GROUP BY
      merchant.merchantid
ORDER BY
      merchant.merchantid ASC
;

edited Jan 16, 2018 at 3:25

answered Jan 16, 2018 at 3:03

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Anonymous Vagrant Over a year ago

thanks. this is exactly what i was looking for. Is there a way to add in all the rows of a table within this query. Say, the merchant.merchantname?

Paul Maxwell Over a year ago

If you need every merchant in the result even if there is no matching transactions then use a LEFT JOIN to the transactions table also need to change the where clause. by the way transaction is a reserved word in some databases so I recommend you always use backticks when referring to that table.

Paul Maxwell Over a year ago

see added query variant

Anonymous Vagrant Over a year ago

Thanks. It works. Last question (honest), im having tough time with including the last remaining statementbalance of statement even if there are no transactions on current day and viewing all the merchants. How would that be corrected in the sql query?

Collectives™ on Stack Overflow

Select query within select query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related