SQL - Nested Selects

[quietstormtw]

Hi all,

I am in desparate need of some assistance with the following SQL statement. What I am attempting to accomplish is to have the Name, ID & Company listed, with the case count listed for each Company.

SELECT
a.name,
a.id,
b.company
(SELECT count(c.cases)
FROM table3 c
WHERE c.id = a.id) Count
FROM
table1 a,
table2 b
WHERE
a.id = b.id

Gives me this as a result:

Name ID Company Case Count
Ronnie 1470 LBL 15
Ronnie 1470 JHM 15
Ronnie 1470 FCL 15
Bobby 2585 LBL 12
Bobby 2585 MAN 12
Bobby 2585 AET 12

What I would like to have is this:

Name ID Company Case Count
Ronnie 1470 LBL 5
Ronnie 1470 JHM 3
Ronnie 1470 FCL 7
Bobby 2585 LBL 4
Bobby 2585 MAN 2
Bobby 2585 AET 6


Again, any assistance in this would be a life saver. Thanks in advance.

 

[jbenson001]

Can you post some sample data?

 

[r937]

if table3 is related to table1 only via id, then your data cannot be like what you have shown

try this by itself --

select a.name                     
     , a.id                       
     , ( SELECT count(c.cases)    
           FROM table3 c          
          WHERE c.id = a.id) Count
  from table1 as a

if this returns what you expect, then you can use it to join to table2

select a2.name
     , a2.id
     , b.company
     , a2.count
  from (
       select a.name
            , a.id
            , ( SELECT count(c.cases)
                  FROM table3 c
                 WHERE c.id = a.id) Count
         from table1 as a
       ) as a2          
inner
  join table2 as b
    on b.id = a2.id

r937.com | rudy.ca

 

[quietstormtw]

Hi Rudy,

Thanks so much for the reply. I guess I scaled my example down too far for me to implement the premise into the acutual code.

I've posted my actual code below. Falling short of asking you re-write it for me. How could I implement the nested selects and inner join into the mess that I've made. I just can't get my head around the inner joins.

SELECT
a.agentnbrid ParagonNo,
ao.careeragtnbr CareerNo,
a.ssnfedid SSN,
a.agtlastname ||', '|| a.agtfirstname AgentName,
b.agtlastname ||', '|| b.agtfirstname BranchManager,
c.carriercode CarrierCode,
cl.classname Classification,
(Select Count(ci.casenbr)
From clientinfo ci, clientagt ca
Where ca.agentnbrid = a.agentnbrid
and ci.casenbr = ca.casenbr
and to_char(ci.dateentered, 'YYYY') >= 2004) Count
FROM
agent a,
agentclassification acl,
agentorganization ao,
agentcontracting ac,
carrier c,
agent b,
carriersked cs,
classification cl
WHERE
a.agentnbrid = acl.agentnbrid
and ao.agentnbrid = a.agentnbrid
and ac.agentnbrid = a.agentnbrid
and ao.mgrid = b.agentnbrid
and ac.carrierskedid = cs.carrierskedid
and cs.carrierid = c.carrierid
and acl.classid = cl.classid
and acl.classid IN (100,101,102,220,221,261,262,20000001)
and ac.currentstatus = 1
and ao.primaryorg = 'Y'
and cs.commpaytype <> 'G'
ORDER BY 4


Thanks in advance!!

 

[r937]

um.... shouldn't you be in the oracle forum?

also, when you re-post the question, the guys over there will want you to identify the primary and foreign keys in each table, in order to analyze the one-to-many relationships, to understand which tables should be joined to which, so that you don't get the inflated counts

r937.com | rudy.ca

 

[JPMontreal]

If I understand well your count should be by company as well, then change your query for something like that:

SELECT
a.name,
a.id,
b.company
(SELECT count(c.cases)
FROM table3 c
WHERE c.id = a.id and c.Company= a.Company ) Count
FROM
table1 a,
table2 b
WHERE
a.id = b.id


Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com