Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2004
    Posts
    15

    Unanswered: Access Query Problems

    Hi, I'm having a problem with creating a query. There are 3 tables: a Software Table, a System Table, and a in between table. The Software Table's software ID is related to the software ID in the in between table. The System table's System ID is related to the System ID in the in between table. I'm trying to return all the software IDs in the Software Table and add a column that shows null unless System ID equals 53. I tried using Access to build it but it only returns the software ID's related to System ID 53. Below is the SQL statement from Access:

    SELECT [in between table].SoftwareID, [System Table].systemID
    FROM [Software Table]
    INNER JOIN ([System Table]
    INNER JOIN [in between table]
    ON [System Table].systemID = [in between table].SystemID)
    ON [Software Table].softwareID = [in between table].SoftwareID
    WHERE ((([System Table].systemID)=53));

    Any help would be appreciated, Thanks

    Rob

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by BlueKalel
    ... add a column that shows null unless System ID equals 53.
    add a column?

    and if System ID is not 53, what goes into this column?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2004
    Posts
    15
    Hi, overall I want to list ALL the software IDs and next to it in another column show either null(blank entry) or 53.

    THE 53 is just an example SYSTEM_ID it could be any number.

    Query output example:
    SOFTWARE_ID SYSTEM_ID
    1 NULL
    2 53
    3 53
    4 NULL
    5 NULL
    6 53
    7 NULL
    8 NULL
    9 53

    Rob

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Rob
    As I see it, all the information you need is in the in between table (so no need to join anything).

    This will give you what you are asking:

    SELECT [in between table].SoftwareID, IIf([SystemID]=53,[SystemID],Null) AS myOutput
    FROM [in between table];

    Something tells me you want to do more than this though.

    Chris

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select [Software Table].softwareID 
         , [in between table].systemID
      from [Software Table] 
    left outer
      join [in between table] 
        on [Software Table].softwareID 
         = [in between table].SoftwareID
       and [in between table].systemID = 53
    Last edited by r937; 09-10-04 at 09:06.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Sep 2004
    Posts
    15
    Chris thanks for your reply, the problem with the sql statement is that the [in between table] will not contain any nulls.

    Example of tables:
    [in between Table]
    unique_id software_id system_id
    01 1 06
    02 1 22
    03 1 04
    04 2 25
    05 2 53
    06 3 53
    07 3 12
    08 4 12
    09 4 25
    10 5 04
    11 6 53

    [System Table]
    system_id system_name
    12 BlueCoat
    53 RedCoat
    04 Rain
    06 Norm

    [Software Table]
    software_id software_name
    1 Windows XP
    2 Office XP
    3 Windows Media Player

    Query output example:
    SOFTWARE_ID SYSTEM_ID
    1 NULL
    2 53
    3 53
    4 NULL
    5 NULL
    6 53
    7 NULL
    8 NULL
    9 53

    I hope this helps people better understand what I'm trying to do.

    Rob

  7. #7
    Join Date
    Sep 2004
    Posts
    15
    r937, Thanks for the reply. I tried your example and got error stating "join expression not supported". Below is the sql statement:

    [Software Table] -> softwareInfo
    [in between Table] -> softwareSystem

    SELECT softwareInfo.softwareID, softwareSystem.SystemID
    FROM softwareInfo
    LEFT OUTER JOIN softwareSystem
    ON softwareInfo.softwareID = softwareSystem.SoftwareID
    and softwareSystem.SystemID=53

    Is this correct? If so why do I keep getting this error?

    Rob

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    access is really flakey

    you need parentheses around the ON condition...
    Code:
      FROM softwareInfo 
    LEFT OUTER 
      JOIN softwareSystem 
        ON (
           softwareInfo.softwareID 
         = softwareSystem.SoftwareID 
       and softwareSystem.SystemID=53 
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Rob

    Chris thanks for your reply, the problem with the sql statement is that the [in between table] will not contain any nulls.
    My SQl will work on the data you have provide and the output you want. Have you tried it ? I took the field names from your original query. If they are different (as suggested in you example tables) then you'd need to change the SQL.

    I wonder if the question you are really asking is:
    List all "in between" records and matching records from system table. But using your sample data the output to that question should be

    1 06
    1 null
    1 04
    2 null
    2 53
    3 53
    3 12
    4 12
    4 null
    5 04
    6 53

    This will gives the above:
    Code:
    SELECT [in between table].SoftwareID, [System table].SystemID
    FROM [in between table] LEFT JOIN [System table] ON [in between table].SystemID = [System table].SystemID;
    HTH
    Chris

Posting Permissions

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