[RESOLVED] vb.net access SQL joins
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
Re: vb.net access SQL joins
Quote:
Originally Posted by
happyme
...
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?
Re: vb.net access SQL joins
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
Re: vb.net access SQL joins
Quote:
Originally Posted by
happyme
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?
Re: vb.net access SQL joins
Try using an INNER join if you only want those rows were xx.colb = 8
Re: vb.net access SQL joins
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.
Re: vb.net access SQL joins
Try to use IIF (for Access) or (CASE...) for MS SQL. Something like:
Code:
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
Re: vb.net access SQL joins
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