Looping Subquery 1

[Newbedude]

Thank you in advance for your suggestions,

I have 2 tables - Drivers & Equations
I need to count how many instances for each driver are in the equations. There is about 500 drivers and 1000 equations. Problem is that the full equation are stored in only 1 column in the equations table.

I need some kind of loop that will search the equations for each driver and output the driver and the count.

I know this is not correct but heres kind of what i need

Obtain list of drivers for loop
Select Driver From tbldrive

Loop - each record idrv - driver name for this instance of the loop
Select count(Equation) as numdrv
From EQL
where Equatuion is not Null and equation Like '%idrv%'

Output
Driver Name=idrv
Count_of_driver= numdrv
End loop

Results IN example
-----------------
Driver | Count
======================
parts 5
costs 150

 

[NoCoolHandle]

Got any sample data or table definitions to share... Can make things easier.

Also what exactly do you want the final resultset to look like?
Make a grid(i seem to be missing something from your previous example (or was it 2)).

Rob

 

[nigelrivett]

Do you have to use a loop?

select tbldrive.Driver , [Count]=count(*)
from tbldrive
join Equations
on Equations.equation Like '%' + tbldrive.Driver + '%'
group by tbldrive.Driver



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Newbedude]

Thanks nigelrivett,
That works just fine for what i needed!
Guess i over looked the obvious.