[RESOLVED] vb.net access SQL joins

[happyme]

Hi All,

I am using VB.NET 2010 Express to query an access mdb database.

I need to join a table like this:

SELECT * FROM
table1 LEFT OUTER JOIN table2 xx ON
table1.cola = xx.cola AND
xx.colb = 8

the xx.colb = 8 part creates an error

I've tried putting the 'xx.colb = 8' part into the WHERE clause but that doesnt produce the same output.

any ideas?

H

[VictorN]

...
I've tried putting the 'xx.colb = 8' part into the WHERE clause but that doesnt produce the same output.

Please, define the same output. The same like in what case?

[happyme]

If I use an outer join
'on table1.cola = xx.cola AND
xx.colb = 8'

then I get a row for every row in table1 that satisfies the where clause. if there are rows where table2.colb = 8 I get those rows, iIf there are no rows where table2.colb = 8 then I get rows filled with NULL values. This is the output I want.

if I put xx.colb = 8 into the WHERE clause instead of the JOIN, if there are no rows where table2.colb = 8 then I get no rows.

sorry not to have been clearer in the first place

[VictorN]

SELECT * FROM
table1 LEFT OUTER JOIN table2 xx ON
table1.cola = xx.cola AND
xx.colb = 8

the xx.colb = 8 part creates an error

Is it a xx.colb or xx.cola?

[sotoasty]

Try using an INNER join if you only want those rows were xx.colb = 8

[happyme]

I need to get a set of rows with null values if the join conditions aren't met, which is why I've selected a left outer join.

in respoinse to VictorN's question,

Is it a xx.colb or xx.cola?
the code in my sample was right: table1.cola = xx.cola AND xx.colb = 8

The thing that bothers me is that this sort of query works fine in the other version of SQL I use with a MSSQL database.

[VictorN]

Try to use IIF (for Access) or (CASE...) for MS SQL. Something like:

SELECT table1*,  IIF(xx.colb = 8, xx.field1, Null), IIF(xx.colb = 8, xx.field2, Null), ...
FROM
table1 LEFT OUTER JOIN table2 xx ON
table1.cola = xx.cola

where field1, field2, ... -the column names of xx

[happyme]

OK fixed it:

SELECT * FROM
(table1 LEFT OUTER JOIN (SELECT * FROM table2 WHERE (colb = 8)) xx
ON
table1.cola = xx.cola)

Thanks for the tip VictorN, I didn't know you could use IIF in access