Hi all, I need to pull data from a bunch of tables, and I *think* outer joins are the way to do it. However, I can't find much on SA's support for outer joins.
What I'm trying to do is pull all items from the Items table, as well as associated attachments and attributes if an item is tied to either one. An item may or may not have attributes (stored in another table), and may or may not have attachments (stored in yet another table). Getting items that have both is what I already have working, but no one told me that items can lack attachments and attributes until today. I'm trying to work out how to do this, especially given that an item could have attributes but no attachments, or attachments but no attributes. This is why an outer join seems to make the most sense. That way, I get all items, with their attributes and attachments in place. If the item lacks either or both, though, those values will simply be null. I can't use query.filter, because filtering will exclude items without these extra bits of data. In fact, I use filtering right now, and I get 24,000 results; I should get over 65,000. I think I want something like: select * from items left outer join attributes on attribute.item_id = items.item_id left outer join attachments on attachment.item_id = items.item_id where items.flag <> 'Y' If I'm thinking about this right, that query will do the job. I'll get items no matter what, so long as the item's flag is not 'Y', and if the item has more data associated with it, that will come along as well. Multiple rows per item (such as four rows for item 1 if item1 has 2 attachments and 2 attributes) aren't a problem. The loop I have to save all this to a CSV file already handles repeated IDs and puts the data where it needs to go. I hope I've explained this well enough. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
