selects within selects 1

[fcoomermd]

I am a little fuzzy on this, and haven't been able to find any really good info.
The first thing I want to do is do a LIKE select... easy enough
Select * from producers where name like '" & request("name&quot & "%'"

Here is my tricky part... Each producer has multiple clients, and when I return the results i would like a count of all the clients the producer has. Keep in mind the clients are in a seperate table joined by an ID
So, I thought there would be another select somewhere within like
Select count(*) from clients c where c.id=p.id
but i am not sure how to join the selects and returning the name, and the count value...
Any ideas?
FC

 

[JohnDTampaBay]

SELECT p.*, count(c.*) AS ClientCount
FROM Producers p
  LEFT OUTER JOIN Clients c ON c.ID = p.ID

I think this will do the trick. This should give you all the producer data and the number of clients serviced by that producer. Good luck.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[fcoomermd]

No go AngelWPB... that didn't work for me... I have been trying this one...

select p.id, x.counter from producers p
Inner join (select pid, count(pid) as counter from clients c group by pid having count(pid)>1) x on p.id=c.pid

Not quite there yet, i am getting this
The column prefix 'c' does not match with a table name or alias name used in the query.

Any seggestions... I cant really get this going....
Not sure what it is ...
FC

 

[sunila7]

Hi fcoomermd,

Try this...

SELECT p.* ,(Select count(pid) from Clients c where c.PID = p.PID ) AS ClientCount
FROM Producer p

Hope it helps..

Sunil

 

[fcoomermd]

Friggin eh.... That worked excellent... Jeeze, and it looks so simple... Thanks alot sunila7!!!
FC

 

[mrccopeland]

I think what you need is to change it to this:

select p.id, x.counter from producers p
Inner join (select pid, count(pid) as counter from clients c group by pid having count(pid)>1) x on p.id=x.pid

You were aliasing inside your select for your join as c but then named it as x (on p.id=c.id should be p.id=x.id).