On Sun, May 6, 2012 at 9:24 AM, Dennis Lee Bieber <wlfr...@ix.netcom.com>wrote:
> On Sun, 6 May 2012 08:16:02 -0700, Mark Phillips > <m...@phillipsmarketing.biz> declaimed the following in > gmane.comp.python.django.user: > > > I have three tables - > > Person: id, first_name, last_name, deleted, last_update > > Email: id, email_address, email_type > > Person_Email: id, person_id, email_id, primary_email > > > > I want to create a view that returns the result of this query as a jason > > object: > > > > select person.id, first_name, last_name, deleted, last_update, > > email_address, email_type, primary_email, from person left join (email, > > person_email) on (person.id=person_email.person_id and > > email.id=person_email.email_id > > ); > > > Is that even valid SQL? I've never seen an example in which a pair > of tables are specified on the right-hand side of a JOIN. > It is valid SQL for MySql - see the documentation for joins ( http://dev.mysql.com/doc/refman/5.1/en/join.html). Quoted from the reference: A table reference is also known as a join expression. The syntax of *table_factor* is extended in comparison with the SQL Standard. The latter accepts only*table_reference*, not a list of them inside a pair of parentheses. This is a conservative extension if we consider each comma in a list of * table_reference* items as equivalent to an inner join. For example: SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c) is equivalent to: SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c) It also works as expected at the mysql prompt with test data. > > I'd probably have it as: > > select > p.id, first_name, last_name, deleted, last_update, > email_address, email_type, primary_email > from person as p > left join person_email as pe on p.id = pe.person_id > left join email as e on e.id = pe.email_id > I'm also curious about "primary_email" vs "email_type". > Not sure whay it matters, but email_type is one of (home, work, mobile, other). Primary_emial is a boolean value to indicate the person's preferred email. > > Oh, and the use of an intersect table (Person_Email) tends to imply > that you have a many-many relationship in which multiple people might be > sharing the same email address. That sounds like a place for trouble -- > or at least a violation of some policies. > In my particular application (youth sports teams), some of the people (youth players) use their parent's email for the team emails, so there are instances when two people share one email address. I have similar tables for addresses and phone numbers. Back to my original question....how would one translate this SQL query into a django query? Thanks, Mark -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
