Forum OpenACS Development: Re: SQL queries 'n TCL

Collapse
5: Re: SQL queries 'n TCL (response to 4)
Posted by Brian Fenton on
Or you could just do an inner join. 😊

select o.* from organizations o, yourtable y
where o.organization_id = y.organization_id
and y.user_id = :foo

Brian

Collapse
6: Re: SQL queries 'n TCL (response to 5)
Posted by Iuri Sampaio on
The select i did is a litle bit tricky, since my organization pkg is set up as non-singleton, so i need to pass the package_id and organization_id in the query to get each organization of each package the user owns.

the query:
?xml version="1.0"?
queryset
fullquery name="orgs_query"
querytext
SELECT
o.organization_id,
o.name,
o.legal_name,
o.reg_number,
o.notes,
ot.type as organization_type
FROM
organizations o,
organization_types ot,
organization_type_map tm
WHERE
o.organization_id in (SELECT ow.organization_id
FROM organization_owner ow
WHERE user_id = :user_id)
AND
o.organization_id = tm.organization_id and
o.packages = :(SELECT ow.package_id
FROM organization_owner ow
WHERE user_id =: user_id)
tm.organization_type_id = ot.organization_type_id and
[template::list::filter_where_clauses -and -name "orgs"]
[template::list::orderby_clause -orderby -name orgs]
/querytext
/fullquery

The attribution is incorrectly made
o.packages = :(SELECT ow.package_id
FROM organization_owner ow
WHERE user_id =: user_id)

MAlte,
I haven't found anything similar to this on the link you post above. Although it's a very good source, thanks a lot.