outer-join

What is the purpose (or use case) for an outer join in SQL?

Is an outer join only used for analysis by the developer? I'm having trouble finding a use case for why you would want to include data in two or more tables that is unrelated or does not "match" your select criteria. ...

LINQ to SQL - Left Outer Join with multiple join conditions

I have the following SQL which I am trying to translate to LINQ: SELECT f.value FROM period as p LEFT OUTER JOIN facts AS f ON p.id = f.periodid AND f.otherid = 17 WHERE p.companyid = 100 I have seen the typical implementation of the left outer join (ie. into x from y in x.DefaultIfEmpty() etc.) but am unsure how to introduce the oth...

Is FULL OUTER JOIN really such a bad thing here?

In general, I believe "FULL OUTER JOIN Considered Harmful", to turn the phrase. Background: http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer-Joins.aspx But I do have a specific situation where it would be really handy: Given: CREATE VIEW Calcs(c1, c2, c3, fordate, ...other columns) AS /* Complicated set of equations...

outer join modelisation in django

Hi, I have a many to many relationship table whith some datas in the jointing base a basic version of my model look like: class FooLine(models.Model): name = models.CharField(max_length=255) class FooCol(models.Model): name = models.CharField(max_length=255) class FooVal(models.Model): value = models.CharField(max_length...

MySQL Join excluding certain records?

Hi, i'm trying to build a join, but I can't get it to work in MySQL, I know there must be a way. I have two tables: 1. Setting and 2. User_Setting (and ofcourse User but that won't be needed here). Structure Setting.setting_id Setting.description Setting.value_type Setting.default_value User_Setting.user_id User_Setting.setting_id ...

Select top one from left outer join

Guys, I have a query where basically select the latest browser that our user used. here is our (simplified) table structure HITS_TABLE ---------- USERID BROWSER HITSDATE USER_TABLE ---------- USERID USERNAME and here is how I query the latest browser that our user used SELECT U.*, H.BROWSER FROM USER_TABLE U CROSS APPLY (SELEC...

How to exclude duplicate rows when joining a table with itself.

Here's a sample table to help illustrate my problem: mysql> select * from test; +----+--------------+--------+ | id | type | siteid | +----+--------------+--------+ | 1 | First Visit | 100 | | 2 | Second Visit | 100 | | 3 | First Visit | 300 | | 4 | First Visit | 400 | | 5 | Second Visit | 500 | | 6 | Sec...

Outer join or dynamic query, which is the best way to go?

I have the following tables (I've simplified the data contained in the tables). RateTable - rate_table_id [int] - rate_table_name [nvarchar(50)] RateTableUsed - rate_used_id [int] - rate_table_id [int] (key to RateTable.rate_table_id) - customer_id [int] RateTableExtra - rate_table_extra_id [int] - rate_ extra_id [int] (key to...

How to join data frames in R (inner, outer, left, right)?

Given two data frames df1 = data.frame(CustomerId=c(1:6),Product=c(rep("Toaster",3),rep("Radio",3))) df2 = data.frame(CustomerId=c(2,4,6),State=c(rep("Alabama",2),rep("Ohio",1))) > df1 CustomerId Product 1 Toaster 2 Toaster 3 Toaster 4 Radio 5 Radio 6 Radio > df...

SQL Server 2005 - Order of Inner Joins

I have a query containing three inner join statements in the Where clause. The query takes roughly 2 minutes to execute. If I simply change the order of two of the inner joins, performance drops to 40 seconds. How can doing nothing but changing the order of the inner joins have such a drastic impact of query performance? I would have th...

NHibernate Left Outer Join SubClass

I have 2 entities products and images. Not all images are product images, and images are a sub class of a file below are my entities. I need to find all of the images that are not associated to a product. I'm new to retrieval of entities and have tried numerous approaches. Any ideas or links would be greatly appreciated. public clas...

Linq and nullable key relationships affecting UNION operation

Here is an example: Lets say I have 3 tables, Countries, People and Cities. City records have a non-nullable foreign key field identifying a country. People records have a nullable foreign key field identifying a country - they may be from an Eastern European country that no longer exists. I want to create a combined list of countries ...

Good object structure for results of my Hibernate left outer join

I need to make an outer join query that retrieves all of the definitions and any properties they have associated with them that are associated with a certain company. I have two Hibernate models objects: class PropertyDefinition { @Id private Long id; @Column private String name; @OneToMany(mappedBy = "propert...

SQL SELECT from multiple tables

How can I get all products from customers1 and customers2 include their customer names? customer1 table cid name1 1 john 2 joe customer2 table cid name2 p1 sandy p2 linda product table pid cid pname 1 1 phone 2 2 pencil 3 p1 pen 4 p2 paper Result should be like this pid cid pname name1 name2 1 1 phone ...

Generate FULL JOIN with LinqToSQL

I have this linq query : (from rapportBase in New_RapportReferencementBases join rapportExtensionAll in New_RapportReferencementExtensionBases on rapportBase.New_RapportReferencementId equals rapportExtensionAll.New_RapportReferencementId into jointureRapportExtension from rapportExtension in jointureRapportExtension.DefaultIfEmpty() ...

Linq to entities Left Join

Hi all, I want to achieve the following in Linq to Entities: Get all Enquires that have no Application or the Application has a status != 4 (Completed) select e.* from Enquiry enq left outer join Application app on enq.enquiryid = app.enquiryid where app.Status <> 4 or app.enquiryid is null Has anyone done this before without using...

LINQ update on joined collections

How do I use LINQ to update objects in one list from objects in a second list? My question is very similar to http://stackoverflow.com/questions/709560/linq-in-line-property-update-during-join except that, in my case, the second list is smaller than the parent list. In other words, I want to update those members of the master collection ...

Efficient MySQL query to find entries in A where not matched in B

I have a couple of tables (products and suppliers) and want to find out which items are no longer listed in the suppliers table. Table uc_products has the products. Table uc_supplier_csv has supplier stocks. uc_products.model joins against uc_suppliers.sku. I am seeing very long queries when trying to identify the stock in the products...

SubSonic Outer Join

There seems to be a Bug with the Outer Join statement in SubSonic 3, or maybe it's just my ignorance, but the following craps out: var Objeto = from t in Table1.All() join su in table2.All() on t.Id equals su.Id join tab2 in Table1.All() on t.PId equals tab2.Id into gj from j in gj.DefaultIfEmpty()...

Is it possible to restrict the results of an outer join?

I've got a scenario where I need to do a join across three tables. table #1 is a list of users table #2 contains users who have trait A table #3 contains users who have trait B If I want to find all the users who have trait A or trait B (in one simple sql) I think I'm stuck. If I do a regular join, the people who don't have trait A ...