Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    1

    Unanswered: Adding a new table to a complex join statement

    All help appreciated.

    I currently use the following select statement as a custom query.

    SELECT * FROM sponinfo RIGHT JOIN (setinfo RIGHT JOIN glinfo ON
    [setinfo].[SET_ID] =[glinfo].[gl_set]) ON [sponinfo].[spon_id]
    =[glinfo].[gl_spon] WHERE ......... (etc, etc, etc....)

    Inventory info is stored in a fourth table called invinfo, key
    [invinfo].inv_id], which equals [glinfo].[gl_id].

    I'm having syntax trouble getting the inventory table joined. Can anyone
    show me the syntax to add the invinfo table to this query?
    Thanks!
    Steve

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    right outer joins and i do not get along -- they're not hard to understand, just backwards

    here is your query written left to right --
    Code:
    select list, the, columns, you, want
      from glinfo
    left outer
      join setinfo
        on glinfo.gl_set = setinfo.SET_ID
    left outer
      join sponinfo
        on glinfo.gl_spon = sponinfo.spon_id
    now to bring in the other table, just add
    Code:
    left outer
      join invinfo
        on glinfo.gl_id = invinfo.inv_id
    ordinarily one sees square brackets and parentheses only in microsoft access, but if you really want them --
    Code:
    select list, the, columns, you, want
      from ( ( glinfo
    left outer
      join setinfo
        on [glinfo].[gl_set] = [setinfo].[SET_ID] )
    left outer
      join sponinfo
        on [glinfo].[gl_spon] = [sponinfo].[spon_id] )
    left outer
      join invinfo
        on [glinfo].[gl_id] = [invinfo].[inv_id]
    rudy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •