post the model. However, it's totally doable (and I use it a lot)
http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Left-outer-join
>>> db.define_table('master_table', Field('f_a')) #lefty table, i.e. the
one with more records
<Table master_table (id,f_a)>
>>> db.define_table('slave_table', Field('f_a')) #righty table, i.e. the
one with less records
<Table slave_table (id,f_a)>
>>> db.master_table.insert(f_a='aaa')
1L
>>> db.master_table.insert(f_a='bbb')
2L
>>> db.slave_table.insert(f_a='aaa')
1L
# usually you'll try to fetch what is in "master" that isn't in "slave"
>>> db(db.slave_table.id == None)._select(db.master_table.ALL, left=db.
slave_table.on(db.master_table.f_a == db.slave_table.f_a))
'SELECT master_table.id, master_table.f_a FROM master_table LEFT JOIN
slave_table ON (master_table.f_a = slave_table.f_a) WHERE (slave_table.id
IS NULL);'
>>> db(db.slave_table.id == None).select(db.master_table.ALL, left=db.
slave_table.on(db.master_table.f_a == db.slave_table.f_a)).as_list()
[{'id': 2L, 'f_a': 'bbb'}]
On Tuesday, May 13, 2014 7:29:36 PM UTC+2, LoveWeb2py wrote:
>
> Hello,
>
> How could I replicate this SQL Query in a controller and display a grid?
>
>
> Here is what I'm trying to accomplish:
> http://stackoverflow.com/questions/15926348/left-join-to-display-records-not-in-right-table
>
> Here is the query:
> SELECT a.* FROM ECRDTL_del AS a
> LEFT JOIN ECRDTL_edit AS b
> ON a.Ecrno = b.Ecrno AND
> a.Cylno = b.CylnoWHERE b.Ecrno IS NULL
>
> I basically just want to show all records where the join doesn't match.
>
> I've tried so many ways, but haven't been able to have success with the DAL
> model.
>
>
>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
