1 Reply Latest reply on Sep 25, 2013 11:32 PM by . Indumon

    Union of two joins in SQL

    Alex Southcott

      I have been attempting to join a list of equipment and a list of contracts that are each in a tab in an Excel worksheets. The contracts need to be filtered out, so as to only include current contracts based on start and end date (in reference to day, which is the parameter). My 2 tables are:

      * just equipment with current contracts or no reference in the contract file

      * equipment which is listed in the contract file, but the dates suggest it's not current, so I've asked it to return NULL as the value for the contract fields.

       

      Each works fine as a standalone, however, when UNION'ed together, the error message says that the number of columns don't match. Can anyone help me out?

       

      SELECT [Equipment$].[Contract Number] AS [Equipment$_Contract Number],
        [Equipment$].[Product Group] AS [Product Group],
        [Equipment$].[Product Number] AS [Product Number],
        [Equipment$].[Serial Number] AS [Serial Number],
        [Contracts$].[Contract Number] AS [Contracts$_Contract Number],
        [Contracts$].[End Date] AS [Contracts$_End Date],
        [Contracts$].[Equipment] AS [Contracts$_Equipment],
        [Contracts$].[Start Date] AS [Contracts$_Start Date],
        [Contracts$].[Value] AS [Contracts$_Value]

      FROM [Equipment$]
        LEFT JOIN [Contracts$] ON [Equipment$].[Serial Number] = [Contracts$].[Equipment]

      WHERE (([Contracts$].[Start Date] < <Parameters.Parameter 1> AND [Contracts$].[End Date] > <Parameters.Parameter 1>)
      OR ([Contracts$].[Start Date] IS NULL))

      UNION

      SELECT [Equipment$].[Contract Number], NULL AS [Equipment$_Contract Number],
        [Equipment$].[Product Group] AS [Product Group],
        [Equipment$].[Product Number] AS [Product Number],
        [Equipment$].[Serial Number] AS [Serial Number],
        [Contracts$].[Contract Number], NULL AS [Contracts$_Contract Number],
        [Contracts$].[End Date], NULL AS [Contracts$_End Date],
        [Contracts$].[Equipment] AS [Contracts$_Equipment],
        [Contracts$].[Start Date], NULL AS [Contracts$_Start Date],
        [Contracts$].[Value], NULL AS [Contracts$_Value]

      FROM [Equipment$]
        LEFT JOIN [Contracts$] ON [Equipment$].[Serial Number] = [Contracts$].[Equipment]

      WHERE ([Contracts$].[Start Date] > <Parameters.Parameter 1> OR [Contracts$].[End Date] < <Parameters.Parameter 1>)

        • 1. Re: Union of two joins in SQL
          . Indumon

          Alex, First block of your select query is creating 1 column and 2nd one has 2 columns. Query will work after  correction

           

          SELECT [Equipment$].[Contract Number] AS [Equipment$_Contract Number],

           

           

          UNION

          SELECT [Equipment$].[Contract Number], NULL AS [Equipment$_Contract Number],