CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2006
    Posts
    28

    [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

  2. #2
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: vb.net access SQL joins

    Quote Originally Posted by happyme View Post
    ...
    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

  3. #3
    Join Date
    Oct 2006
    Posts
    28

    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

  4. #4
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: vb.net access SQL joins

    Quote Originally Posted by happyme View Post
    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

  5. #5
    Join Date
    Sep 2000
    Location
    FL
    Posts
    1,452

    Re: vb.net access SQL joins

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

  6. #6
    Join Date
    Oct 2006
    Posts
    28

    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

  7. #7
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    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

  8. #8
    Join Date
    Oct 2006
    Posts
    28

    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
  •  





Click Here to Expand Forum to Full Width

Featured