Hi!
I wrote a small program to test this problem.
I have 3 tables:
CREATE TABLE pricegroups (id integer primary key);
CREATE TABLE products (id integer primary key, name text);
CREATE TABLE products_pricegroups (id integer primary key, product_id
integer, pricegroup_id integer, percent integer);
I would like to perform the following query:
select * from pricegroups
left join (select * from products_pricegroups
where products_pricegroups.product_id=1) as a
on (pricegroups.id=a.pricegroup_id)
How can I accomplish it in mysql?
This is where i am now:
#!/usr/bin/env python
import os
import sqlobject
class Product(sqlobject.SQLObject):
class sqlmeta:
table = 'products'
name = sqlobject.StringCol()
class PriceGroup(sqlobject.SQLObject):
class sqlmeta:
table = 'pricegroups'
percents = sqlobject.MultipleJoin('ProductPriceGroup', joinColumn =
'pricegroup_id')
class ProductPriceGroup(sqlobject.SQLObject):
class sqlmeta:
table = 'products_pricegroups'
productId = sqlobject.ForeignKey('Product', dbName = 'product_id')
pricegroupId = sqlobject.ForeignKey('PriceGroup', dbName =
'pricegroup_id')
percent = sqlobject.IntCol()
if __name__ == '__main__':
builder = sqlobject.sqlite.builder()
connection = builder('join.db')
connection.debug = 1
sqlobject.sqlhub.processConnection = connection
pgl = list(PriceGroup.select(
join = sqlobject.sqlbuilder.LEFTJOINOn(
PriceGroup, ProductPriceGroup,
PriceGroup.q.id == ProductPriceGroup.q.pricegroupId,
),
))
for pg in pgl:
print pg
The next step is to apply the WHERE clause, but I have no idea how to do
it...
Thanks for any help:
Imre Horvath
------------------------------------------------------------------------------
_______________________________________________
sqlobject-discuss mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
