Join with results from both tables

Hi

I'm just learning sqlobject and find it pretty neat!

Suppose I have this:
from sqlobject import *

class Variant2(SQLObject):
    class sqlmeta:
        style = Style(longID=True)
    ref = StringCol()
    alt = StringCol()
    SubjectID = StringCol()

class Subject2(SQLObject):
    FamilyID = StringCol()
    IndivID = StringCol( alternateID=True, length=50 )  # do not know if this is long enough
    Sex = StringCol()

connection = connectionForURI(Minerva_URI)
connection.debug = True
sqlhub.processConnection = connection
Variant2.dropTable( True, )
Variant2.createTable()
Subject2.dropTable( True, )
Subject2.createTable()

Variant2(
    ref = 'A',
    alt = 'C',
    SubjectID = 'Sub1' )

Variant2(
    ref = 'A',
    alt = 'G',
    SubjectID = 'Sub2' )

Subject2( FamilyID = '', IndivID = 'Sub1', Sex = '1' )
Subject2( FamilyID = '', IndivID = 'Sub2', Sex = '2' )

for var in Variant2.select(
    """ subject2.indiv_id = Variant2.SubjectID""",
    clauseTables=['subject2']):
    print var

How would I generate a query that joins Variant2 and subject2? Like this:

SELECT Variant2.Variant2_id, Variant2.ref, Variant2.alt, Variant2.AAC, Variant2.SubjectID, subject2.Sex FROM subject2, Variant2 WHERE  subject2.indiv_id = Variant2.SubjectID;

Also, how could I define SubjectID as a foreign key that references indiv_id?

Thanks
Arthur




---

Arthur Goldberg
Associate Professor of Psychiatry
Seaver Autism Center and Icahn Institute for Genomics & Multiscale Biology
Icahn School of Medicine at Mount Sinai
Seaver Center, Room ABE-33
212-241-4229
[email protected]<mailto:[email protected]>
Follow us on Twitter @IcahnInstitute<https://twitter.com/IcahnInstitute>

------------------------------------------------------------------------------
HPCC Systems Open Source Big Data Platform from LexisNexis Risk Solutions
Find What Matters Most in Your Big Data with HPCC Systems
Open Source. Fast. Scalable. Simple. Ideal for Dirty Data.
Leverages Graph Analysis for Fast Processing & Easy Data Exploration
http://p.sf.net/sfu/hpccsystems

_______________________________________________
sqlobject-discuss mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss