Loop in SQL

[gulbano]

Hey all,

I am new in SQL my problem is that I have to select one by one to perform certain function can any one tell me how to loop through the records of a table. I have googled it a lot but still not able to sort out this problem. Please help me

Thanks

[debasisdas]

what exactly you are trying to do ?

[gulbano]

I have a table of employees a table of purchased goods and another of goods to sell i want to calculate price of a whole order if it contains 2 or more than 2 items against one employee for this purpose i have to check it for each employee and i have to generate a report

[debasisdas]

It is better to write a procedural block.

what is the relation between the tables ?

[gulbano]

many to many

[debasisdas]

no, i was asking about the table structures and their relations.

[gulbano]

ok employee table contains information about employees( id, fist name, last name, salary, age etc) goods table contains information about items available in store (is, name, quantity available, price etc) the third table contains information of order (employee_id, item_id, quantity and price)

[debasisdas]

Why not try using JOIN .

Join all the three tables and retrieve the data

[abelLazm]

just solve a similar problem why don't you too go for the same solution check this out

[gulbano]

Thanks debasisdas for your concern

Why not try using JOIN .

I have tried join but it is not working because i have to count items and calculate their price one by one for each employee. Can you please tell me how can join be helpful

Thanks abelLazm i have checked this page and also one of your post in another thread can you please explain how can i use fetch statement as it is very lengthy ans scary line of code

[abelLazm]

The page link i gave you before contains all the details of fetch statement and check this thread i think changing names of tables and columns according to your database and using sum(price) --instead of avg(balance) in line 24 will work for you

[debasisdas]

try something like this

select e.first_name
from employees e, goods g, orders o
where e.id = o.employee_id
and g.id = o.item_id

you need to modify this to suit your requirement.

[gulbano]

Thanx abellazm i have checked your code it is working for me but i need it in grid anyway thanx you two for helping :)