[Fwd: Re: [Zope-DB] Need help passing table data as a variable to next SQL method]

[Cliff Ford]

There are at least two approaches you could use, depending on how much
data you are returning and how you want it displayed:

1. You could use a single SQL query to return Company and Employee data,
by joining the Company and Employee tables. You don't say whether you
are new to Python too, but it would be best to process the query results
with a Python script. The query would look something like this:

select Companies.CompanyName, Employees.* from Companies
join Employees on Companies.CompanyID=Employees.CompanyID
order by CompanyName, EmployeeName

and the Python would look something like this:

CoID = -1
for result in theQuery():
     if result.CompanyID != CoID:
         # Company has changed so print some Company data
         print "%d, %s" % (result.CompanyID, result.CompanyName)
         CoID = result.CompanyID
     # print the Employee data
     print "%d, %s" % (result.EmployeeID, result.EmployeeName)

2. You could have two queries, passing the results of the first as
parameters to the second. The queries might be:

select * from Companies
order by CompanyName

select * from Employees where CompanyID=<dtml-sqlvar CompanyID type=int>
order by EmployeeName

The Python code would then look like this:

for Company in CompaniesQuery():
     #print out the Company data
     print "%d, %s" % (Company.CompanyID, Company.CompanyName)
     for Employee in EmployeesQuery(CompanyID=Comapny.CompanyID)
         # print the Employee data
         print "%d, %s" % (Employee.EmployeeID, Employee.EmployeeName)

Note that I have not tested any of this - and it is just a skeleton. I
hope it helps.

Cliff

Gil Pangilinan wrote:
> Hello,
> I'm fairly new to Zope, Python, and SQL so bear with me.  I have 
> successfully created the first table to be viewed but now having trouble.
> 
> The first SQL query returned a table of  each row that has 4 pieces of 
> data.
> What I'm struggling with now is taking each of those 4 pieces of data 
> and passing them as arguments for the next SQL method I need to run.
> 
> Just to give a generic example, this first table displays companies and 
> their ID's.  I need to pass the company name and ID's to the next SQL 
> method that will then bring up all the employees for that specific 
> company and branch.  Any advice pointing me in the right direction will 
> be greatly appreciated.
> 
> Thanks,
> Gil
> 
> _______________________________________________
> Zope-DB mailing list
> Zope-DB at zope.org
> http://mail.zope.org/mailman/listinfo/zope-db