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

[Cynthia Kiser]

In almost all circumstances, you will want to use the first approach
Cliff outlined for you. Joins are generally faster than doing several
separate queries. The one thing you may need to watch out for is if
you need to list any Companies that don't have Employees - or vice
versa. If you do, then you will need to learn the appropriate syntax
for doing an outer join. 

Quoting Cliff Ford <Cliff.Ford at ed.ac.uk>:
> 
> 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
> 
> _______________________________________________
> Zope-DB mailing list
> Zope-DB at zope.org
> http://mail.zope.org/mailman/listinfo/zope-db