-
January 14th, 2013, 06:11 AM
#1
[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
I'm using .NET Framework 3.5
I'm planning to be spontaneous tomorrow
-
January 14th, 2013, 06:32 AM
#2
Re: vb.net access SQL joins
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?
Victor Nijegorodov
-
January 14th, 2013, 06:43 AM
#3
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
I'm using .NET Framework 3.5
I'm planning to be spontaneous tomorrow
-
January 14th, 2013, 07:01 AM
#4
Re: vb.net access SQL joins
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?
Victor Nijegorodov
-
January 14th, 2013, 07:38 AM
#5
Re: vb.net access SQL joins
Try using an INNER join if you only want those rows were xx.colb = 8
-
January 14th, 2013, 08:00 AM
#6
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.
I'm using .NET Framework 3.5
I'm planning to be spontaneous tomorrow
-
January 14th, 2013, 08:13 AM
#7
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
Victor Nijegorodov
-
January 14th, 2013, 08:25 AM
#8
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
I'm using .NET Framework 3.5
I'm planning to be spontaneous tomorrow
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|