How would I join on multiple columns from multiple tables

SELECT A.col1,B.col2,C.col3

FROM (TABLE1 A  LEFT JOIN TABLE2 B ON A.key1=B.key1 AND A.key2=B.ky2 AND A.ke3='some value)

           LEFT JOIN TABLE3 C ON A.key1=C.key1 AND (C.key2=B.key2 OR C.key3>'some value')

WHERE A.col5='..' OR  (A.col7=',,' AND C.col5<='...)

Field name col1, col2 and key1 and key2 are aributrary 

Thank you
 

ayoussef, look at the join syntax on our home page, it's very simple. You can use OR and AND in your where clause like this:

Where( (A | B) & (C | D) )

Notice the | and & operators. You sql is using the old syntax, just do the joins, and put other conditions in the Where clause. However, you could try put those conditions in the ON() portion as I think that might work as well.

It looks like you are confusing Join conditions with Where clauses. A Join condition links two tables using columns in each table combined with a logical operators. Typically, the columns are either equal or not equal, and the columns used are PK/FK columns, although that is not required.

This:

A.ke3='some value

does not join anything. It could be part of a Where clause. Here is how to join tables using multiple columns, then add in your where conditions.

OrderCollection collection = new OrderCollection();

OrderQuery oq = new OrderQuery("oq");
CustomerQuery cq = new CustomerQuery("cq");

oq.Select(oq.OrderID, oq.OrderDate, cq.CustomerName);
oq.LeftJoin(cq).On(oq.CustID == cq.CustomerID,
    oq.CustSub == cq.CustomerSub);
oq.Where(cq.Active == true &
    oq.OrderDate >= "2000-01-01 00:00:00");

collection.Load(oq);

Thanks David,

The situation I have is, we have a master codes table (MasterCodes) made of code_type, code_id, code_description so we do not have a table for each code type, i.e type='01' Race code, type='02' Marital status,

for a patient table (Patients) with columns, last,first,race_code,maritalStatuscode

SELECT A.last,A.first,C.code_description

FROM Patients A LEFT JOIN  MasterCodes C ON (A.race_code=C.code_id AND C.code_type='01')

WHERE .... 

this would be a left join returning all records from patient tables meeting the where criteria and if a code is not found MasterCode I would get null for code description

But,  

SELECT A.last,A.first,C.code_description

FROM Patients A LEFT JOIN  MasterCodes C ON (A.race_code=C.code_id)

WHERE C.code_type='01'

would turn the join into inner join and only get the records that have a mastercode type ='01'

so if code_type='01' is missed by any chance  I would not get a list of patients,

This is legacy app that patients can define their race code, we need the customer to see the patient record even if the race code is not defined

I hope this make sense why I need the col='some value in the join clause

Thank you 

Do you think it is worth adding it to future release like 2008

That would be great

Just wanted to support the proposal. I have a similar situation (ParentId, ParentTableName), and it's a big problem for me. I know you are busy, but I'd like to ask you to add the feature to the current release.

Thank you,

Leonid

Support for literals in the "On" portion of a Join was added for SQL Server and tested last night. Adding it to the other providers and testing should be complete before this weekend's release. The syntax I showed above (using commas to separate predicates) is no longer supported. You must provide a logical operator, either an "&" or "|". From our JoinFixture:

CustomerCollection collection = new CustomerCollection();

CustomerQuery cq = new CustomerQuery("cq");
EmployeeQuery eq = new EmployeeQuery("eq");

cq.Select(cq.CustomerName, eq.LastName);
cq.LeftJoin(eq).On(cq.StaffAssigned == eq.EmployeeID &
    eq.Supervisor == 1);
cq.OrderBy(cq.CustomerName.Ascending);

Assert.IsTrue(collection.Load(cq));
Assert.AreEqual(56, collection.Count);
Assert.AreEqual("Doe", collection[1].GetColumn("LastName"));

Produces:

SELECT cq.[CustomerName],eq.[LastName]  
FROM [ForeignKeyTest].[dbo].[Customer] cq 
LEFT JOIN [ForeignKeyTest].[dbo].[Employee] eq 
ON (cq.[StaffAssigned] = eq.[EmployeeID] 
AND eq.[Supervisor] = 1) 
ORDER BY cq.[CustomerName] ASC

You make me love EntitySpaces. The second time in a week the important feature gets implemented super fast !!!

I'm still at the very beginning of learning all aspects of EntitySpaces, but I have a few years of experience with IdeaBlade, so I have things in hands to compare. You should really try their OQL language to really appreciate the might of EntitySpaces Dynamic SQL!

Thank you,

Leonid

David,

Just tried the new syntax in my project, and found a problem. In my case I have two fields I join the child table to the parent: ParentId (int) and ParentTable (string) so the join looks like this:

                pQuery.InnerJoin(innerQuery).On(

                    new esQueryItem(parentQuery, "Id", esSystemType.Int32) == new esQueryItem(innerQuery, "ParentId", esSystemType.Int32) &

                    new esQueryItem(innerQuery, "ParentTable", esSystemType.String) == "Image");

The join clause the framework generates is (I use SQL 2005):

Leonid, why are you creating esQueryItems with the new operator? That syntax is not supported. For now just put single quotes inside your double quotes around "Image", that will make it work. I guess I've never used complex "where" type operations in the On() clause, we will look at this for our mid-December maintenance release, however, I'm very confused as to where you are getting this syntax from? It's very confusing.  If that is what our syntax looked like I don't think I'd use EntitySpaces

Thank you Mike, it's working.

The idea of the code came from this post (thanks to Martin) http://community.entityspaces.net/forums/thread/4874.aspx 

The legacy application requires a generalized query builder that accepts table names and some filtration criteria. It is currently implemented as a dynamic sql generation function, but ... I have to tell you that EntitySpaces syntax you mentioned is a pleasure to work with in comparison to what I have in hands . Also, ES gives something else that is difficult to achieve in other means - QueryBase class, for example does some work for me by automatically adding security filtration conditions to the query. I use a similar new esQueryItem() technique down there.

So, I'm a little scared by you saying 'that syntax is not supported' because it's really important for me and I'd like to see it always being there.

Leonid

Is multiple join criteria supported within LeftJoins in ES 2008?   I am trying to add multiple join criteria to a LeftJoin (SQL Server) using "&" and I'm getting syntax errors on the SQL statement.

Thanks,
Matt