Is it possible to include a sub-query inside a select statement?

Question

I have the following three tables:

dbo_CURRENCYRATES
+-------------+---------+---------------+
|CURRENCY_ID  |BUY_RATE |DATE_EFFECTIVE |
+-------------+---------+---------------+
|GBP          |1.5      |01/01/2000     |
|USD          |2.5      |01/01/2000     |
|EUR          |0.5      |01/01/2000     |
|GBP          |1.7      |01/01/2017     |
|USD          |2.7      |01/01/2017     |
|EUR          |0.7      |01/01/2017     |
+-------------+---------+---------------+

dbo_DISCOUNTRATES
+-------------+--------------+
|DISCOUNT     |DISCOUNT_RATE |
+-------------+--------------+
|50           |0.5           |
|25           |0.25          |
|35           |0.35          |
+-------------+--------------+

dbo_CUSTOMER
+-------+---------------+---------+----------+
|ID     |NAME           |CURRENCY | DISCOUNT |
+-------+---------------+---------+----------+
|1      |Widgets INC    |USD      |50        |
|2      |Widgets GMBH   |EUR      |35        |
|3      |Widgets PLC    |GBP      |25        |
+-------+---------------+---------+----------+

and a query

CurrentExchangeRate
+--------------------------------------------+
SELECT 
   a.CURRENCY_ID
  ,a.BUY_RATE
FROM dbo_CURRENCYRATES AS t 
   INNER JOIN 
      (
       SELECT 
          CURRENCY_ID
         ,MAX(DATE_EFFECTIVE) AS MaxDate 
       FROM dbo_CURRENCYRATES 
       GROUP BY dbo_CURRENCYRATES.CURRENCY_ID
      ) AS tm 
          ON (a.CURRENCY_ID = b.CURRENCY_ID) 
          AND (a.DATE_EFFECTIVE = b.MaxDate);

I have a select query which selects data from dbo_CUSTOMER which includes currency and discount.

SELECT 
   dbo_CUSTOMER.ID
  ,dbo_CUSTOMER.NAME
  ,dbo_CUSTOMER.CURRENCY_ID AS [CURRENCY]
  ,BUY_RATE
  ,dbo_CUSTOMER.DISCOUNT
  ,DISCOUNT_RATE
FROM dbo_CUSTOMER
WHERE ID = '1';

Is is possible to include in this query, a sub-query which lets me get the BUY_RATE for the currency (from a separate query) and the the appropriate discount rate by querying another table?

I have a separate query to get the currency rate, as I need to have the latest value for the specific currency.

So, in pseudo code, can I do this:

SELECT 
   dbo_CUSTOMER.ID
  ,dbo_CUSTOMER.NAME
  ,dbo_CUSTOMER.CURRENCY_ID AS [CURRENCY]
  ,dbo_CUSTOMER.DISCOUNT
  ,(
    SELECT BUY_RATE 
    FROM CurrentExchangeRate 
    WHERE CurrentExchangeRate.CURRENCY_ID = [CURRENCY]
   ) as BUY_RATE
  ,(
    SELECT DISCOUNT_RATE 
    FROM dbo_DISCOUNTRATES 
    WHERE dbo_DISCOUNTRATES.DISCOUNT = dbo_CUSTOMER.DISCOUNT
   ) as DISCOUNT_RATE
FROM dbo_CUSTOMER
WHERE ID = '1';

Is is possible to do this, firstly in a query and secondly in an access database?

Thanks

yes, you can (in a query - i would expect in an access db as well). — AlexT82
In the example you show the subquery would have to return a single record - the same value for all records. This is often done with an aggregate query or TOP 1. Since you have dynamic criteria in the subquery, I expect you really need to JOIN queries to table. Review allenbrowne.com/subquery-01.html#TopN — June7

AlexT82 AlexT82 · Accepted Answer · 2017-08-29T17:40:14

Yes, you can. Only tip you need to know in your sub queries, for what you need, is that you need to select with a TOP 1, and ORDER BY DESC on the date field.

Is it possible to include a sub-query inside a select statement?

1 Answers