I want to do build a query showing me all Sites with Items (for various
types of Item).
This works well when checking just a single type of item:
table = db.org_site
itable = db.inv_item
query = (table.id > 0) & \
(itable.site_id == table.id)
sites = db(query).select(table.name,
distinct=True)
However if I want to add a 2nd type of item then it doesn't show any values
since there are no rows with both sorts of item in:
rtable = db.recv_item
query = (table.id > 0) & \
((itable.site_id == table.id) | \
(rtable.site_id == table.id))
I can't just do a Left Outer Join as I deliberately wish to exclude sites
which have no Items at all.
Anyone able to solve this conundrum?
(Other than by simply doing the queries separately & merging the results)
Thanks,
Fran.
