How can I create a query for a full outer join across a M2M relationchip using the django QuerySet API? It that is not supported, some hint about creating my own manager to do this would be welcome. Edited to add: @S.Lott: Thanks for the enlightenment. The need for the OUTER JOIN comes from the application. It has to generate a report...
How to rewrite this: select tab1.id, tab2.id, tab3.id from tab1, tab2, tab3 where tab1.col1 = tab2.col1(+) and tab2.col2 = tab3.col2(+); using OUTER JOIN syntax? ...
SELECT pe.prodtree_element_id prodID, pe.prodtree_element_name_s, li.line_name, av2.value FROM prodtree_element pe LEFT JOIN prodtree_link pl ON pe.prodtree_element_id = pl.to_prodtree_node_id LEFT JOIN line li ON pe.line_code = li.line_code INNER JOIN attribute_values av ON av.attribute_definition_id = #st...
I'm trying to do an Outer Join in Symfony. Here's the bit of code that seems like it would work if Criteria supported OUTER_JOIN: $user = sfContext::getInstance()->getUser(); $petCriteria = new Criteria(); $petCriteria->add(PetInfoPeer::ACCOUNT_ID, $user->getProfile()->getAccountId()); $petCriteria->add(LostPetPeer::PET_INFO_ID, Criteri...
So I have the following code: return from a in DBContext.Acts join artist in DBContext.Artists on a.ArtistID equals artist.ID into art from artist in art.DefaultIfEmpty() select new Shared.DO.Act { ID = a.ID, Name = a.Name, Artist = new Shared.DO.Artist ...
I can do it in sybase and I can do it in oracle, but I'm not seeing how to do it in mysql. I've got this: (please restrain yourself from re-formatting my sql, last time somebody did that they changed it so it wasn't the same and made the question meaningless) select table1.id from table1 inner join table2 on (table1.id = table2.i...
Hey Stackers, Since SQLite does not support RIGHT OUTER JOINS I pose the following challenge (read: invitation to do my work for me): Refactor this query so it no longer utilises SQLite-unsupported constructs like RIGHT/FULL OUTER JOINs. SELECT strings.*, translations.text FROM translations INNER JOIN ...
How do I write a sub-select in LINQ. If I have a list of customers and a list of orders I want all the customers that have no orders. This is my pseudo code attempt: var res = from c in customers where c.CustomerID ! in (from o in orders select o.CustomerID) select c ...
I have a database table that looks like: ForeignId: int Key: varchar Value: varchar Where ForeignId and Key constitute a unique primary key I can easily determine the total set of keys defined for a given set of document with SELECT DISTINCT [Key] FROM [Table] WHERE [ForeignId] IN (...) What I would like to do however is to further...
Hi, I have two tables in an XML Dataset. T1, T2. Each of the tables has a ID column. T1 has a list of Customers T2 has a list of Orders I want to build a LINQ query that returns only the ID of the customers that do not have orders. In other words customer ID's that do not exist in the T2 table. Oh yea, I'm using C# Thanks! ...
I've got a table and want to outer-join another table, getting only the first row (the one with lowest nr) of the second table using Oracle 10g. Edit: nr is unique within an id Table x Table y id id nr code 1 1 1 B 2 1 2 A 3 2 2 A Expected result: id nr code 1 1 B ...
I have two tables that are something like this: Main table: id (int), title (varchar), etc. Sub-table: main_table_id (foreign key into main table), tag (varchar), etc. There can be zero or more subtable rows for a given row in the main table. I want to do a query that will return every row of the main table, with the columns of the...
Hi, I have a question about a SQL statement generated by a LINQ2SQL query. I have two database tables (VisibleForDepartmentId is a foreign key): AssignableObject Department ---------------------- ------------ AssignableObjectId ┌────> DepartmentId AssignableObjectType │ VisibleForDepartmentId ───┘ ...
The RIGHT JOIN on this query causes a TABLE ACCESS FULL on lims.operator. A regular join runs quickly, but of course, the samples 'WHERE authorised_by IS NULL' do not show up. Is there a more efficient alternative to a RIGHT JOIN in this case? SELECT full_name FROM (SELECT operator_id AS authorised_by, full_name ...
The following two queries are returning different results, to my surprise: SELECT * FROM foo JOIN bar ON bar.id=foo.bar_id JOIN baz ON baz.id=foo.baz_id LEFT JOIN zig ON zig.foo_id=foo.id; and: SELECT * FROM foo LEFT JOIN zig ON zig.foo_id=foo.id JOIN bar ...
Given the following tables I'd like to return the localised text for given culture or the text for the default culture where no row exist for the given culture. So with the folowing data Resources ID Name 1 Donkey 2 Elephant LocaleStrings ID CultureID ResID LocaleText 1 1 1 Donkey 2 1 2 Elepha...
I'm creating a small forum. Attempting to run SElECT... JOIN... query too pick up information on the individual posts, plus the last reply (if any). As part of my desire to do everything the hard way, this covers five tables (only columns revelant to this issue are being stated) commentInfo referenceID | referenceType | authorID | ...
Assuming this is the correct Ansi SQL syntax for a left outer join: SELECT * FROM employee LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID And this is the correct Ansi SQL syntax for a right outer join: SELECT * FROM employee RIGHT OUTER JOIN department ON employee.Departme...
This is coming from converting MSSQL to MySql. The following is code I'm trying to get to work: CREATE TEMPORARY TABLE PageIndex ( IndexId int AUTO_INCREMENT NOT NULL PRIMARY KEY, ItemId VARCHAR(64) ); INSERT INTO PageIndex (ItemId) SELECT Paths.PathId FROM Paths, ((SELECT Paths.PathId FROM AllUsers, Paths ...
I've looked all around and spent way to long trying to convert this SQL statement into a Linq statement in VB. I'm sure it would be a good example for others out there - the statement is trying to pull products that have a many-to-many relationship with product categories, and the categories have a hierarchy of parents/children. Here is...