sqlobject-discuss — All things SQLObject-related

Re: [SQLObject] select only specified columns

[Oleg B. <ph...@ph...>]

On Thu, Jul 01, 2010 at 08:52:38PM +0200, Petr Jake?? wrote:
> Creat View in the SQL and than use it in the SQLObject as an ordinary table.
> You can handle some more complex SQL SELECT  within the view.

   +1

> Of course it is read only (I am on Firebird - maybe some other databases
> behave differently)

   Yes, there are VIEWs that support updating.

   Another way is to use ViewSQLObject. See http://sqlobject.org/Views.html

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] select only specified columns

[Petr J. <pet...@tp...>]

Creat View in the SQL and than use it in the SQLObject as an ordinary table.
You can handle some more complex SQL SELECT  within the view.

Of course it is read only (I am on Firebird - maybe some other databases
behave differently)

HTH

Petr

On 1 July 2010 20:38, Mark <mar...@gm...> wrote:

>
> > +1VIEW is, IMHO, en excelent way to go, when the SQLObject is close to
> its
> > limits.Petr
>
> How do you create and use a view in SQLObject?
>
> And is not a view a tradeoff on response time vs DB size? If I create a
> separate
> table with a million rows I may add 50mb to the DB size, but if I use a
> view the
> DB has to select out the view each time and that 50mb may not cache.
>
> Mark
>
>
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>

[SQLObject] Sorting a list of selected SQLObjects

[Mark <mar...@gm...>]

I just set up my own forum, and am attempting to sort a user's subscriptions
by the last post in each thread(BBTopic). However the sorted function is 
failing saying lambda only takes 1 argument but was given 2. I use this
sorted function on lists of non SQLObject objects and it works fine for me.

Can you use sorted with a list returned from a select? 

Or is there a way for me to rewrite this? Perhaps using orderBy?
 

        subs_sel = list(BBSubs.select(BBSubs.q.user_id==1))
        for sub in subs_sel:
            sub.topic = BBTopic.get(sub.topic_id)
            sub.post  = BBPost.get(sub.topic.last_post_id)
            sub.forum = BBForum.get(sub.topic.forum_id)
          
        subs = sorted(subs_sel, lambda x: x.topic.last_post_id)

Re: [SQLObject] select only specified columns

[Mark <mar...@gm...>]

> +1VIEW is, IMHO, en excelent way to go, when the SQLObject is close to its 
> limits.Petr

How do you create and use a view in SQLObject? 

And is not a view a tradeoff on response time vs DB size? If I create a separate 
table with a million rows I may add 50mb to the DB size, but if I use a view the 
DB has to select out the view each time and that 50mb may not cache. 

Mark

Re: [SQLObject] select only specified columns

[Petr J. <pet...@tp...>]

+1
VIEW is, IMHO, en excelent way to go, when the SQLObject is close to its
limits.

Petr


On 1 July 2010 18:28, Oleg Broytman <ph...@ph...> wrote:

> On Thu, Jul 01, 2010 at 03:48:38PM +0000, Mark wrote:
> > Even if you weren't using SQLObject I believe it would make more
> > sense to create a separate name/id table and then only select
> > from the 40 column table when you know what row you want.
>
>    Or use a VIEW.
>
> Oleg.
> --
>     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
>           Programmers don't die, they just GOSUB without RETURN.
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>

Re: [SQLObject] select only specified columns

[Oleg B. <ph...@ph...>]

On Thu, Jul 01, 2010 at 03:48:38PM +0000, Mark wrote:
> Even if you weren't using SQLObject I believe it would make more 
> sense to create a separate name/id table and then only select 
> from the 40 column table when you know what row you want.

   Or use a VIEW.

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] select only specified columns

[Mark <mar...@gm...>]

Even if you weren't using SQLObject I believe it would make more 
sense to create a separate name/id table and then only select 
from the 40 column table when you know what row you want.

Mark

Re: [SQLObject] Problem with "driver has already been registered"

[Oleg B. <ph...@ph...>]

On Tue, Jun 22, 2010 at 01:16:15PM +0200, Julien Syx wrote:
> I agree with you, the error seems to be a double import because the message
> says that the driver already exists.

   Try to add debugging log/print. Log sys.path and sys.modules before
importing sqlobject, log something before the exception...

> I am completely stuck without ideas. My code is available via the svn if you
> want to review my code.

   I doubt I will have a spare minute any time soon, sorry.

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Problem with "driver has already been registered"

[Julien S. <nep...@gm...>]

As you can see on the svn, scops.tac is the server.
There is just imports of twisted before the sys.path, and nothing else.

I tested to import in a pythonshell sqlobject to see if it was uninstalled
from system (in python site-packages) and it seems to be ok. So i have only
sqlobject in my package.

I set nothing in my __init__.py, so there is nothing imported by default,
right ?

In the main app (scops.tac), i import scopssql, and scopssql import
sqlobject ... and it seems to be the only import in all the project
actually. (and the error message seems to be in this file which is the first
import). And i don't see where it can be imported first.

I agree with you, the error seems to be a double import because the message
says that the driver already exists.

I am completely stuck without ideas. My code is available via the svn if you
want to review my code.

Thanks a lot

On Tue, Jun 22, 2010 at 12:15 PM, Oleg Broytman <ph...@ph...> wrote:

> On Tue, Jun 22, 2010 at 12:06:35PM +0200, Julien Syx wrote:
> > I don't know where it can be imported twice.
> > Does the sys.path manipulation auto import modules ?
>
>    By itself - no, but there could be two different imports in different
> parts of the application before and after sys.path change.
>
> Oleg.
> --
>     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
>           Programmers don't die, they just GOSUB without RETURN.
>
>
> ------------------------------------------------------------------------------
> ThinkGeek and WIRED's GeekDad team up for the Ultimate
> GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the
> lucky parental unit.  See the prize list and enter to win:
> http://p.sf.net/sfu/thinkgeek-promo
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>

Re: [SQLObject] select only specified columns

[Oleg B. <ph...@ph...>]

On Tue, Jun 22, 2010 at 12:09:22PM +0200, Imre Horvath wrote:
> Thank you, that's what I've looking for.
> In my case, I only fill details view for one record only, 30-40 columns,
> it's fast enough with one-column queries too.

   Oops, I've forgotten - lazyColumns by itself prevents loading of columns
but when you touch a row SQLObject will load all columns for the row. To
load only one column also set

    class sqlmeta:
        cacheValues = False

> But I wonder if it's possible to override get method to temporary switch
> off lazyColumns?

   No. lazyColumns is taken into account when constructing the row, so it's
meaningless to set it afterwards.

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Problem with "driver has already been registered"

[Oleg B. <ph...@ph...>]

On Tue, Jun 22, 2010 at 12:06:35PM +0200, Julien Syx wrote:
> I don't know where it can be imported twice.
> Does the sys.path manipulation auto import modules ?

   By itself - no, but there could be two different imports in different
parts of the application before and after sys.path change.

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] select only specified columns

[Imre H. <ble...@gm...>]

Thank you, that's what I've looking for.
In my case, I only fill details view for one record only, 30-40 columns,
it's fast enough with one-column queries too.
But I wonder if it's possible to override get method to temporary switch
off lazyColumns?
I mean:

class Customer(SQLObject):
	def get(self, id):
		self.lazyColumns = False
		entity = Customer.get(id)
		self.lazyColumns = True
		return entity

Imre

2010. 06. 22, kedd keltezéssel 13.42-kor Oleg Broytman ezt írta:
> On Tue, Jun 22, 2010 at 11:20:39AM +0200, Imre Horvath wrote:
> > Is it possible to select only a few columns from a table?
> 
>    No, but you can lazily selects columns using lazyColumns=True; with this
> SQLObject doesn't initially draw any column, only id's; but when you touch a
> row and ask for a column SQLObject executes a specific one-row one-column
> SELECT query. If you are going to touch a million rows this lead to at least
> few millions queries; perhaps it'd be faster to select all columns at once,
> but YMMV.
> 
> Oleg.

Re: [SQLObject] Problem with "driver has already been registered"

[Julien S. <nep...@gm...>]

Thanks for your quick answer.
I don't know where it can be imported twice.
Does the sys.path manipulation auto import modules ?

Here is an access to the code :
http://svn.scops.fr/Scops/Serveur
login : mlsqlobject
pass : scops

it's accessible through webbrowser.

Thanks a lot.

On Tue, Jun 22, 2010 at 11:37 AM, Oleg Broytman <ph...@ph...> wrote:

> On Mon, Jun 21, 2010 at 11:34:50PM +0200, Julien Syx wrote:
> >     from scops.sql.scopssql import connectSQL
> >   File "./scops/sql/scopssql.py", line 1, in <module>
> >     from sqlobject import *
> >   File "./scops/sql/sqlobject/__init__.py", line 8, in <module>
> >     from main import *
> >   File "./scops/sql/sqlobject/main.py", line 32, in <module>
> >     import dbconnection
> >   File "./scops/sql/sqlobject/dbconnection.py", line 964, in <module>
> >     import firebird
> >   File "./scops/sql/sqlobject/firebird/__init__.py", line 7, in <module>
> >     registerConnection(['firebird', 'interbase'], builder)
> >   File "./scops/sql/sqlobject/dbconnection.py", line 918, in
> > registerConnection
> >     "A driver has already been registered for the URI scheme %s" %
> uriScheme
> > exceptions.AssertionError: A driver has already been registered for the
> URI
> > scheme firebird
>
>    I suspect it could be because sqlobject was imported twice, probably
> because of manipulations with sys.path.
>
> Oleg.
> --
>     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
>           Programmers don't die, they just GOSUB without RETURN.
>
>
> ------------------------------------------------------------------------------
> ThinkGeek and WIRED's GeekDad team up for the Ultimate
> GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the
> lucky parental unit.  See the prize list and enter to win:
> http://p.sf.net/sfu/thinkgeek-promo
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>

Re: [SQLObject] select only specified columns

[Oleg B. <ph...@ph...>]

On Tue, Jun 22, 2010 at 11:20:39AM +0200, Imre Horvath wrote:
> Is it possible to select only a few columns from a table?

   No, but you can lazily selects columns using lazyColumns=True; with this
SQLObject doesn't initially draw any column, only id's; but when you touch a
row and ask for a column SQLObject executes a specific one-row one-column
SELECT query. If you are going to touch a million rows this lead to at least
few millions queries; perhaps it'd be faster to select all columns at once,
but YMMV.

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Problem with "driver has already been registered"

[Oleg B. <ph...@ph...>]

On Mon, Jun 21, 2010 at 11:34:50PM +0200, Julien Syx wrote:
>     from scops.sql.scopssql import connectSQL
>   File "./scops/sql/scopssql.py", line 1, in <module>
>     from sqlobject import *
>   File "./scops/sql/sqlobject/__init__.py", line 8, in <module>
>     from main import *
>   File "./scops/sql/sqlobject/main.py", line 32, in <module>
>     import dbconnection
>   File "./scops/sql/sqlobject/dbconnection.py", line 964, in <module>
>     import firebird
>   File "./scops/sql/sqlobject/firebird/__init__.py", line 7, in <module>
>     registerConnection(['firebird', 'interbase'], builder)
>   File "./scops/sql/sqlobject/dbconnection.py", line 918, in
> registerConnection
>     "A driver has already been registered for the URI scheme %s" % uriScheme
> exceptions.AssertionError: A driver has already been registered for the URI
> scheme firebird

   I suspect it could be because sqlobject was imported twice, probably
because of manipulations with sys.path.

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] select only specified columns

[Imre H. <ble...@gm...>]

Hi everyone!

Is it possible to select only a few columns from a table?
My primary problem is speed when i select all rows from a big table (>1
mill. record).
But i only need 2-3 columns from it. I think it's because all other
columns & related objects are selected.

FYI: The exact scenario is:
Customers table: id, name, lots of other data.
For a master/detail form, i need to fill the list with id and name only.
When the user selects a row in the list, i get the customer object via
id, and fill the details form.

Thanks for advance:
Imre Horvath

[SQLObject] Problem with "driver has already been registered"

[Julien S. <nep...@gm...>]

Hi everyone.
I'm working on a project and i use sqlobject.

I decided to create a package which contains all my functions, classes and
modules.

The package is like this :

/application.tac
/package
/package/sql/scopssql.py -> contains my class to connect to the database,
and classes to use/create it. (
/package/sql/sqlobject/ -> contains the sqlobject module.
/package/servers/

In my .tac, i do :
import sys
sys.path.append(".")
sys.path.append("./scops")

In the file scopssql.py, i do a :
from sqlobject import *

When i launch my app :


seraf@tagada-server:/opt/Scops/Serveur# twistd -noy scops.tac

Traceback (most recent call last):

  File "/usr/lib/python2.6/dist-packages/twisted/application/app.py", line
693, in run

    runApp(config)

  File "/usr/lib/python2.6/dist-packages/twisted/scripts/twistd.py", line
23, in runApp

    _SomeApplicationRunner(config).run()

  File "/usr/lib/python2.6/dist-packages/twisted/application/app.py", line
411, in run

    self.application = self.createOrGetApplication()

  File "/usr/lib/python2.6/dist-packages/twisted/application/app.py", line
494, in createOrGetApplication

    application = getApplication(self.config, passphrase)

--- <exception caught here> ---

  File "/usr/lib/python2.6/dist-packages/twisted/application/app.py", line
505, in getApplication

    application = service.loadApplication(filename, style, passphrase)

  File "/usr/lib/python2.6/dist-packages/twisted/application/service.py",
line 390, in loadApplication

    application = sob.loadValueFromFile(filename, 'application', passphrase)

  File "/usr/lib/python2.6/dist-packages/twisted/persisted/sob.py", line
210, in loadValueFromFile

    exec fileObj in d, d

  File "scops.tac", line 16, in <module>

    from scops.sql.scopssql import connectSQL

  File "./scops/sql/scopssql.py", line 1, in <module>

    from sqlobject import *

  File "./scops/sql/sqlobject/__init__.py", line 8, in <module>

    from main import *

  File "./scops/sql/sqlobject/main.py", line 32, in <module>

    import dbconnection

  File "./scops/sql/sqlobject/dbconnection.py", line 964, in <module>

    import firebird

  File "./scops/sql/sqlobject/firebird/__init__.py", line 7, in <module>

    registerConnection(['firebird', 'interbase'], builder)

  File "./scops/sql/sqlobject/dbconnection.py", line 918, in
registerConnection

    "A driver has already been registered for the URI scheme %s" % uriScheme

exceptions.AssertionError: A driver has already been registered for the URI
scheme firebird

Failed to load application: A driver has already been registered for the URI
scheme firebird

Have you an idea why it fails ?

Thanks a lot !

Re: [SQLObject] Creating SQL connection to Postgres over SSL

[Oleg B. <ph...@ph...>]

On Sat, Jun 05, 2010 at 01:04:37AM -0500, Aaron Pestel wrote:
> postgresql://cumin@localhost/cumin?[sslmode]=require, but that doesn't 
> seem to work.

   Currently it's impossible. I added the request to my TODO list.

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Creating SQL connection to Postgres over SSL

[Frank W. <fra...@no...>]

Hi Aaron,

from what I have seen in the code my guess is that any parameter added to the connection uri is passed along to the database driver, if it isn´t amongst the ones specified in http://sqlobject.org/SQLObject.html#declaring-a-connection

having said that, your approach 
postgresql://cumin@localhost/cumin?sslmode=require

should be working, except for the square brackets, just omit them.
my uris look like this for example:
mysql://user:pass@host/dbname?charset=utf8&use_unicode=1&debug=True

HTH,
Frank



Am 05.06.2010 um 08:04 schrieb Aaron Pestel:

> Hello,
> 
> I'm working with a Python application that is using SQLObject to connect 
> to Postgres.  However, we need the connection from Python to the 
> Postgres DB to be over SSL.
> 
> It looks like the Postgres Python driver has an "sslmode" that can be 
> set [1].  However, I'm having difficulty seeing how that could be set 
> through the PostgresConnection [2].  I have a Java background rather 
> than a Python background, so that may be part of the problem.
> 
> I think the connection is being created like this:
> 
> connectionForURI("postgresql://cumin@localhost/cumin").getConnection()
> 
> Is it possible for me to add something like listed in [1] to make the DB 
> connection over SSL?  I tried 
> postgresql://cumin@localhost/cumin?[sslmode]=require, but that doesn't 
> seem to work.
> 
> [1]  
> http://python.projects.postgresql.org/docs/1.0/driver.html#connection-keywords 
> 
> [2]  
> http://svn.colorstudy.com/SQLObject/trunk/sqlobject/postgres/pgconnection.py 
> 
> 
> Thanks!
> 
> Aaron Pestel
> 
> ------------------------------------------------------------------------------
> ThinkGeek and WIRED's GeekDad team up for the Ultimate 
> GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the 
> lucky parental unit.  See the prize list and enter to win: 
> http://p.sf.net/sfu/thinkgeek-promo
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

[SQLObject] Creating SQL connection to Postgres over SSL

[Aaron P. <ap...@re...>]

Hello,

I'm working with a Python application that is using SQLObject to connect 
to Postgres.  However, we need the connection from Python to the 
Postgres DB to be over SSL.

It looks like the Postgres Python driver has an "sslmode" that can be 
set [1].  However, I'm having difficulty seeing how that could be set 
through the PostgresConnection [2].  I have a Java background rather 
than a Python background, so that may be part of the problem.

I think the connection is being created like this:

connectionForURI("postgresql://cumin@localhost/cumin").getConnection()

Is it possible for me to add something like listed in [1] to make the DB 
connection over SSL?  I tried 
postgresql://cumin@localhost/cumin?[sslmode]=require, but that doesn't 
seem to work.

[1]  
http://python.projects.postgresql.org/docs/1.0/driver.html#connection-keywords 

[2]  
http://svn.colorstudy.com/SQLObject/trunk/sqlobject/postgres/pgconnection.py 


Thanks!

Aaron Pestel

Re: [SQLObject] Vacation

[Juan M. S. <vic...@gm...>]

Have a nice time off Oleg:)

Juan Manuel

From: Oleg Broytman <ph...@ph...>
To: SQLObject discussion <sql...@li...>
Date: Wednesday 26 May 2010
> Hello. I'm leaving the Net and diving into the Real Life. Will be back at
> 14th of June.
> 
> Oleg.

[SQLObject] Vacation

[Oleg B. <ph...@ph...>]

Hello. I'm leaving the Net and diving into the Real Life. Will be back at
14th of June.

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] left join with subquery and WHERE

[Imre H. <ble...@gm...>]

On Fri, 2010-05-14 at 19:21 +0400, Oleg Broytman wrote:
> On Fri, May 14, 2010 at 04:43:46PM +0200, Imre Horvath wrote:
> > sqlite> select * from pricegroups 
> >    ...> left join (select * from products_pricegroups
> >    ...> where products_pricegroups.product_id=1) as a 
> >    ...> on (pricegroups.id=a.pricegroup_id);
> 
>    I don't understan what is aliased as 'a'. The subquery? I tried to take
> it into parentheses:
> 
> select * from pricegroups 
>    left join ((select * from products_pricegroups
>    where products_pricegroups.product_id=1) as a)
>    on (pricegroups.id=a.pricegroup_id);
> 
> bug got an error:
> 
>    SQL error near line 1: no such column: a.pricegroup_id.
> 
> Oleg.

Yes, the subquery has to be aliased.

I found a less elegant but working solution:
Select all pricegroups, then select the groups_pricegroups of a product,
and combine the 2 results manually...

Imre

Re: [SQLObject] left join with subquery and WHERE

[Oleg B. <ph...@ph...>]

On Fri, May 14, 2010 at 04:43:46PM +0200, Imre Horvath wrote:
> sqlite> select * from pricegroups 
>    ...> left join (select * from products_pricegroups
>    ...> where products_pricegroups.product_id=1) as a 
>    ...> on (pricegroups.id=a.pricegroup_id);

   I don't understan what is aliased as 'a'. The subquery? I tried to take
it into parentheses:

select * from pricegroups 
   left join ((select * from products_pricegroups
   where products_pricegroups.product_id=1) as a)
   on (pricegroups.id=a.pricegroup_id);

bug got an error:

   SQL error near line 1: no such column: a.pricegroup_id.

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] left join with subquery and WHERE

[Imre H. <ble...@gm...>]

On Fri, 2010-05-14 at 18:24 +0400, Oleg Broytman wrote:
> On Fri, May 14, 2010 at 06:22:05PM +0400, Oleg Broytman wrote:
> > On Fri, May 14, 2010 at 03:37:46PM +0200, Imre Horvath wrote:
> > > select * from pricegroups 
> > > left join (select * from products_pricegroups
> > > where products_pricegroups.product_id=1) as a 
> > > on (pricegroups.id=a.pricegroup_id)
> 
>    Does the query work at all in MySQL?
> 
> Oleg.

It's in sqlite, but it works.
It's also works in postgres.
The goal is to select all pricegroups for a product, even if it's not
set for the given product.
this is my test db:

sqlite> .schema
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);

sqlite> select * from products;
1|a
2|b
3|v

sqlite> select * from pricegroups;
1
2
3
4
5
6
7
8

sqlite> select * from products_pricegroups;
1|1|1|10
2|1|2|20
3|2|4|20

sqlite> select * from pricegroups 
   ...> left join (select * from products_pricegroups
   ...> where products_pricegroups.product_id=1) as a 
   ...> on (pricegroups.id=a.pricegroup_id);
1|1|1|1|10
2|2|1|2|20
3||||
4||||
5||||
6||||
7||||
8||||

sqlite> select * from pricegroups 
   ...> left join (select * from products_pricegroups
   ...> where products_pricegroups.product_id=2) as a 
   ...> on (pricegroups.id=a.pricegroup_id);
1||||
2||||
3||||
4|3|2|4|20
5||||
6||||
7||||
8||||


I hope i was clear

Regards:
Imre Horvath