Using SubQuery as Field Column

Question

I have the query which has subquery as field column but i want to use this field column in other place.

SELECT c.country_code                                                    AS 
       country_code, 
       c.dial_code                                                       AS 
       dial_code, 
       (SELECT r.destination 
        FROM   region r 
        WHERE  r.country_code = c.country_code 
               AND r.dial_code = c.dial_code)                            AS 
       destination, 
       c.start_time, 
       c.duration, 
       c.call_type, 
       c.customer_prefix                                                 AS 
       customer_prefix, 
       c.vendor_prefix                                                   AS 
       vendor_prefix, 
       (SELECT Round(r.rate, 3) 
        FROM   rate r 
               INNER JOIN region re 
                       ON r.region_id = re.id 
               INNER JOIN account_prefix ap 
                       ON r.account_prefix_id = ap.id 
        WHERE  re.country_code = c.country_code 
               AND re.dial_code = c.dial_code 
               AND ap.prefix = c.customer_prefix 
               AND ap.prefix_type = 0)                                   AS 
       **customer_rate**, 
       (SELECT Round(r.rate, 3) 
        FROM   rate r 
               INNER JOIN region re 
                       ON r.region_id = re.id 
               INNER JOIN account_prefix ap 
                       ON r.account_prefix_id = ap.id 
        WHERE  re.country_code = c.country_code 
               AND re.dial_code = c.dial_code 
               AND ap.prefix = c.vendor_prefix 
               AND ap.prefix_type = 1)                                   AS 
       **vendor_rate**, 
       (SELECT Round(r.rate, 3) 
        FROM   rate r 
               INNER JOIN region re 
                       ON r.region_id = re.id 
               INNER JOIN account_prefix ap 
                       ON r.account_prefix_id = ap.id 
        WHERE  re.country_code = c.country_code 
               AND re.dial_code = c.dial_code 
               AND ap.prefix = c.customer_prefix 
               AND ap.prefix_type = 0) - (SELECT Round(r.rate, 3) 
                                          FROM   rate r 
                                                 INNER JOIN region re 
                                                         ON r.region_id = re.id 
                                                 INNER JOIN account_prefix ap 
                                                         ON r.account_prefix_id 
                                                            = ap.id 
                                          WHERE 
       re.country_code = c.country_code 
       AND re.dial_code = c.dial_code 
       AND ap.prefix = c.vendor_prefix 
       AND ap.prefix_type = 1) AS **unit_profit**, 
       unit_profit * duration 
FROM   cdr c 
LIMIT  100;

As you can see, I want to use the unit_profit, customer_rate, and vendor_rate. How to achieve it ?

EDIT:

Any tutorial that showing join on view ?

You cannot directly use aliases defined in the same SELECT list. You will need to use an outer query, or (if your RDBMS supports it) CTEs. — PM 77-1
– PM 77-1, Commented Mar 8, 2013 at 2:02
Please elaborate more on the outer query. What is CTE ? I'm using mysql database. — Ivan
– Ivan, Commented Mar 8, 2013 at 2:35
Erwin Brandstetter's answer gives an example of outer query (additional SELECT FROM all inside queries. CTE stands for Common Tables Expressions (I was thinking about WITH) but apparently MySQL have no support for it. — PM 77-1
– PM 77-1, Commented Mar 8, 2013 at 2:41

Bhavesh G · Accepted Answer · 2020-07-23 19:22:10Z

3

What you need to do is to take all those subqueries done inside the flieds, and create a join with the CDR table.

This will greatly improve the performance of the query and response time. What you are doing now is executing 3 queries for each for the records at CDR. Ff this table (CDR) have just a few records is fine, but if not this could consume lot of processor, memory and disk I/O.

The trick to do the "join" and show the info in the same format is to join 3 times the same subquery but with a different alias.

select  c.country_code, customer_rate_table.customer_rate 
from CDR c
left outer join on ( SELECT Round(r.rate, 3) customer_rate , re.country_code, 
                       re.dial_code, re.dial_code, ap.prefix  
               FROM   rate r 
               INNER JOIN region re 
                       ON r.region_id = re.id 
               INNER JOIN account_prefix ap 
                       ON r.account_prefix_id = ap.id 
               WHERE ap.prefix_type = 1
        
) customer_rate_table
ON  customer_rate.country_code = c.country_code 
AND customer_rate.dial_code = c.dial_code 
AND customer_rate.prefix = c. customer_prefix 
left outer join on ( {Same as above but with the right fields} ) vendor_rate_table
ON  vendor_rate_table.country_code = c.country_code 
AND vendor_rate_table.dial_code = c.dial_code 
AND vendor_rate_table.prefix = c.vendor_prefix

and then the next table...

This code is not complete but I think gives an explanation on what you need to do.

Thanks!

@leo

edited Jul 23, 2020 at 19:22

Bhavesh G

3,0285 gold badges47 silver badges69 bronze badges

answered Mar 8, 2013 at 2:17

lemil77

3411 silver badge8 bronze badges

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

3 Comments

Ivan Over a year ago

What is this techniques called ? Normally, we join using PK and FK relationship but this times we joining using table. It is not executable on mysql.

lemil77 Over a year ago

This is basically a join,but instead of using tables is using ad-hoc views with their own PKs

Ivan Over a year ago

Join using view rather than table. What is the official name ?

Erwin Brandstetter · Accepted Answer · 2013-03-08 02:16:21Z

1

Correlated subqueries like you have in your query generally suck when it comes to performance. Since you only retrieve 100 rows, it shouldn't be too bad, but if you want it faster you have to rewrite your query.

The problem at hand can be fixed easily with:

SELECT *, unit_profit * duration AS my_calc
FROM (
   -- your query here
   -- just without "unit_profit * duration"
   -- and maybe without redundant column aliases
   ) AS sub

answered Mar 8, 2013 at 2:16

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

6 Comments

Ivan Over a year ago

Can you provide a complete example ?

Erwin Brandstetter Over a year ago

@peterwkc: This is a complete example. Follow the instructions and insert your query as subquery.

Ivan Over a year ago

I think your approach is same with lemil77 but difference is view on join statement and view on from clause.

Ivan Over a year ago

What is this techniques called ?

Erwin Brandstetter Over a year ago

@peterwkc: Subquery or sub-select. Very basic stuff. A view is something different. Unlike most other widespread RDBMS MySQL does not support CTEs (Common Table Expression). Last link is to the Postgres manual, since MySQL doesn't have it.

|

Collectives™ on Stack Overflow

Using SubQuery as Field Column

2 Answers 2

3 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related