When/How to use correlated Subqueries

[Sahil123]

Hi All,

1 thing i really struggle with is working with SUB QUERIES and HOW/WHEN to USE CORRELATED SUB QUERIES

Can anyone give examples to when/how to use them as i struggle when to use these and when to use in Select/from or where clause

Can someone be kind enough to break this down and give an example and What kind of things to look out for to help me with this

Thank You

 

[Kyle123]

A correlated sub query is a Query that is executed for every row that is returned by the query which depends on a value in that row.

SELECT customers.id, 
       customers.NAME, 
       (SELECT Count(*) 
        FROM   orders 
        WHERE  orders.customerid = customers.id) [Number of Orders] 
FROM   customers;

In the above the [Number of Orders] is executed for every record in Customers, using the Id from the customer record in that row.

Typically correlated queries are a last resort since they are painfully inefficient, you can usually accomplish the same task with a Join:

SELECT customers.id, 
       customers.NAME, 
       Count(orders.id)  [Number of Orders] 
FROM   customers 
       LEFT JOIN orders 
              ON orders.customerid = customers.id 
GROUP  BY customers.id, 
          customers.NAME

Where the join here is only evaluated once. Here's a working example: SQL Fiddle

 

[Sahil123]

A correlated sub query is a Query that is executed for every row that is returned by the query which depends on a value in that row.

SELECT customers.id, 
       customers.NAME, 
       (SELECT Count(*) 
        FROM   orders 
        WHERE  orders.customerid = customers.id) [Number of Orders] 
FROM   customers;

In the above the [Number of Orders] is executed for every record in Customers, using the Id from the customer record in that row.

Typically correlated queries are a last resort since they are painfully inefficient, you can usually accomplish the same task with a Join:

SELECT customers.id, 
       customers.NAME, 
       Count(orders.id)  [Number of Orders] 
FROM   customers 
       LEFT JOIN orders 
              ON orders.customerid = customers.id 
GROUP  BY customers.id, 
          customers.NAME

Where the join here is only evaluated once. Here's a working example: SQL Fiddle

You are really kind and I appreciate you taking time out to go through this for me
Thank You

So to summarize, Joins could replace Correlated queries.

Say I had a query like this:

select  Trim(a.TP_ACTIVITY) as "AQ Code",
           a.TS_CREATED as "Time Created",
           Trim(a.ST_TP_PRIORITY_140) as "Priority",
           decode(a.NO_LEGAL_ENTITY,null,0,a.NO_LEGAL_ENTITY) as "Legal Entity No",
        decode(a.NO_PROPERTY,null,0,a.NO_PROPERTY) as "Property No",
        decode(a.NO_ACCOUNT,null,0,a.NO_ACCOUNT) as "Account No",
        c.NM_PREFERRED as "Employee",
        b.AM_CURRENT_BAL as "Current Bal"
from    MI166_A a,
        CIS.TVP008ACCOUNT b,
        CIS.TVP036LEGALENTITY c
where   a.NO_ORGANISATION = 2000315
and     a.NO_ACCOUNT = b.NO_ACCOUNT (+)
and     a.CD_COMPANY_SYSTEM = b.CD_COMPANY_SYSTEM (+)
and     a.NO_EMPL_ASSGN = c.NO_LEGAL_ENTITY (+)
and     a.CD_COMPANY_SYSTEM = c.CD_COMPANY_SYSTEM (+)
order by 2

Now based on this above, all I wanted to do is add a count column in my select query to give the count of the data retrieved. How could I add this as currently I get an error
I have tried to add count(*) or count(a.TP_ACTIVITY) with no luck

Can you give an example/things to look out for to when I may need to use sub queries in the select/from or where statement
Many Thanks

 

[Kyle123]

What database are you using? That's some odd syntax

Is: CIS.TVP008ACCOUNT a table name? or is CIS an identifier of some kind?

 

[Kyle123]

I think you want something like:

(Guessing oracle)

SELECT Trim(a.tp_activity)                          		AS [AQ Code], 
       a.ts_created                                 		AS [Time Created], 
       Trim(a.st_tp_priority_140)                   		AS [Priority], 
       Decode(a.no_legal_entity, NULL, 0, a.no_legal_entity) 	AS [Legal Entity No], 
       Decode(a.no_property, NULL, 0, a.no_property)         	AS [Property No], 
       Decode(a.no_account, NULL, 0, a.no_account)           	AS [Account No], 
       c.nm_preferred                               		AS [Employee], 
       b.am_current_bal                             		AS [Current Bal], 
       Count(*) 
FROM   mi166_a 
       LEFT OUTER JOIN cis.tvp008account b 
               ON a.cd_company_system = b.cd_company_system 
                  AND a.no_account = b.no_account 
       LEFT OUTER JOIN cis.tvp036legalentity c 
               ON a.no_empl_assgn = c.no_legal_entity 
                  AND a.cd_company_system = c.cd_company_system 
WHERE  a.no_organisation = 2000315 
GROUP  BY a.tp_activity, 
          a.ts_created, 
          a.st_tp_priority_140, 
          a.no_legal_entity, 
          a.no_property, 
          a.no_account, 
          c.nm_preferred, 
          b.am_current_bal

 

[Sahil123]

I think you want something like:

(Guessing oracle)

SELECT Trim(a.tp_activity)                                  AS [AQ Code], 
       a.ts_created                                         AS [Time Created], 
       Trim(a.st_tp_priority_140)                           AS [Priority], 
       Decode(a.no_legal_entity, NULL, 0, a.no_legal_entity)     AS [Legal Entity No], 
       Decode(a.no_property, NULL, 0, a.no_property)             AS [Property No], 
       Decode(a.no_account, NULL, 0, a.no_account)               AS [Account No], 
       c.nm_preferred                                       AS [Employee], 
       b.am_current_bal                                     AS [Current Bal], 
       Count(*) 
FROM   mi166_a 
       LEFT OUTER JOIN cis.tvp008account b 
               ON a.cd_company_system = b.cd_company_system 
                  AND a.no_account = b.no_account 
       LEFT OUTER JOIN cis.tvp036legalentity c 
               ON a.no_empl_assgn = c.no_legal_entity 
                  AND a.cd_company_system = c.cd_company_system 
WHERE  a.no_organisation = 2000315 
GROUP  BY a.tp_activity, 
          a.ts_created, 
          a.st_tp_priority_140, 
          a.no_legal_entity, 
          a.no_property, 
          a.no_account, 
          c.nm_preferred, 
          b.am_current_bal

Again thank you

yes Cis.TVP008ACCOUNT is a table

I have now learnt based on your query that i could use brackets to add a name without speech marks i.e as "PROPERTY NUMBER"
to [PROPERTY NUMBER]

Can you please explain to me how this Join works as i am still learning

FROM mi166_a
LEFT OUTER JOIN cis.tvp008account b
ON a.cd_company_system = b.cd_company_system
AND a.no_account = b.no_account
LEFT OUTER JOIN cis.tvp036legalentity c
ON a.no_empl_assgn = c.no_legal_entity
AND a.cd_company_system = c.cd_company_system
WHERE a.no_organisation = 2000315

I know the first Join joins mi66_a to cis.tvp008account

How does the 2nd join work, is it joining both mi66_a and cis.tvp008account to cis.tvp036legalentity? or just joining 1 of those tables?

I see this was done outside the join, any reason for this
WHERE a.no_organisation = 2000315

Thank you once again

Is there a way i could get a unique count column just for the a.tp_activity as just want to get a count of the unique tp_activity data

Thank you

 

[Kyle123]

I can't really explain it any better than the top answer here: syntax - SQL left join vs multiple tables on FROM line? - Stack Overflow

An outer join returns all records from one table and matching records from another (with nulls for non matches)

mi166_a 
       LEFT OUTER JOIN cis.tvp008account b 
               ON a.cd_company_system = b.cd_company_system 
                  AND a.no_account = b.no_account

Returns all the rows from A with any matching rows from B where a.cd_company_system = b.cd_company_system AND a.no_account = b.no_account .

       LEFT OUTER JOIN cis.tvp036legalentity c 
               ON a.no_empl_assgn = c.no_legal_entity 
                  AND a.cd_company_system = c.cd_company_system

Returns all the rows so far (All A and matches from B) and any Records from C that match a.no_empl_assgn and a.cd_company_system respectively.

The additional criteria could have been placed here:

mi166_a 
       LEFT OUTER JOIN cis.tvp008account b 
               ON a.cd_company_system = b.cd_company_system 
                  AND a.no_account = b.no_account 
                  AND a.no_organisation = 2000315

But that's what WHERE clauses are for, it keeps all the filter criteria together as much as possible.

I don't really know what you want with your Count as you haven't been explicit enough, this get you unique tp_activity with Count of occurrences:

SELECT 
             TRIM(a.tp_activity) [AQ Code],
             Count(*) [Count of AQ Code]
FROM
             mi166_a 
WHERE
             a.no_organisation = 2000315
GROUP BY
             a.tp_activity

 

[Sahil123]

Hi kyle

do you teach or willing to teach me sql/vba classes if i were to pay?