Left join in SubSonic Problem

Question

I'd like to perform an left join in this query.The query like: select ... from tableA left join tableB on tableA.Cola=tableB.Colb and tableB.Colc='some value'

I want to know how to perform the "and" condition,i try to coding like:

new SubSonic.Select().From("tableA").LeftOuterJoin ("tableB","Colb","tableA","Cola").AndExpression("Colc").IsEqualTo("some value")

but generated statement is not wanted.

Please give me some help.By the way,the version of subsonic is 2.1. I'm a beginner for use subsonic. Any help would be appreciated and sorry for my poor english,hope you can understand!

Answer 1

This may not be exactly what you want , but the best way to do things like this in subsonic is with views, So create the select as a view and then use the view object in your code. In 3+ the linq makes it alot easier to accomplish what you are trying

Answer 2

It looks to me like the and part of your query should really be a where condition (you're trying to filter your results based on the value of tableB.Colc being equal to 'some value'). So what I think your sql query should look like is:

select ... 
  from tableA left join tableB on tableA.Cola=tableB.Colb 
  where tableB.Colc='some value' or tableB.Colc is null

If that is the case then in SubSonic you would do:

new SubSonic.Select()
   .From("tableA").LeftOuterJoin ("tableB","Colb","tableA","Cola")
   .Where("Colc").IsEqualTo("some value")
   .Or("Colc").IsNull()

Answer 3

TO Adam: Thanks your answer. I think there is some problem with your solution.For example:

tableA:

id | name |status

1 | xx | 1

2 | yy | 0

3 | zz | null

---

tableB:

keyid | keyvalue |keytype

0 | inactive | personstate
1 | dimission | personstate
1 | aaa | gamestate
2 | bbb | gamestate

then i want

select id,name,keyvalue as status from tableA left outer join tableB on status=keyid and keytype='personstate'

this result has 3 rows.

But use

select id,name,keyvalue as status from tableA left outer join tableB on status=keyid where keytype='personstate'

return only 2 rows. There are not equal exactly.

so how can i do that?