Not Able to Execute UNION with Software Application

Transact-SQL
1

Hello Community,

I'm using an application called DbForge to execute SQL Queries. However, it doesn't handle UNION's very well, so I need to improvise.

I would like to do a UNION with the following tables:
CREATE TABLE DataTransfer.Sales2015 (
MakeName NVARCHAR(100) NULL
,ModelName NVARCHAR(150) NULL
,CustomerName NVARCHAR(150) NULL
,CountryName NVARCHAR(150) NULL
,Cost MONEY NULL
,RepairsCost MONEY NULL
,PartsCost MONEY NULL
,TransportInCost MONEY NULL
,SalePrice NUMERIC(18, 2) NULL
,SaleDate DATETIME NULL
) ON [PRIMARY]
GO

CREATE TABLE DataTransfer.Sales2016 (
MakeName NVARCHAR(100) NULL
,ModelName NVARCHAR(150) NULL
,CustomerName NVARCHAR(150) NULL
,CountryName NVARCHAR(150) NULL
,Cost MONEY NULL
,RepairsCost MONEY NULL
,PartsCost MONEY NULL
,TransportInCost MONEY NULL
,SalePrice NUMERIC(18, 2) NULL
,SaleDate DATETIME NULL
) ON [PRIMARY]
GO

The data looks like the following:

union
union.png958×121 7.32 KB

The actual data is as follows:

When I run the following query I get the error:
The multi-part identifier "Sales2016.MakeName" could not be bound. Query.sql 3 3

SELECT
  Sales2015.MakeName
 ,Sales2016.MakeName
 ,Sales2015.ModelName
 ,Sales2016.ModelName
 ,Sales2017.MakeName
 ,Sales2017.ModelName
FROM DataTransfer.Sales2015 UNION SELECT s.MakeName, s.ModelName FROM DataTransfer.Sales2015 s
    ,DataTransfer.Sales2016 UNION SELECT s.MakeName, s.ModelName FROM DataTransfer.Sales2016 s

Can someone please let me know where I'm going wrong?

Carlton

2

Please provide:

  • sample data (at least 3 rows from each table) in the form of insert statements (for all 3 tables)
  • expected output from the sample data you provide
1 Like
3

I am not familiar with DbForge - since I don't use it...but this sounds like an issue with the tool where you are writing the query.

If you can open a straight query window and create the code yourself - it would work. If you do not have that capability then you might want to use SSMS.

4

What you are doing here is also super confusing for example

SELECT s.MakeName, s.ModelName 
FROM DataTransfer.Sales2015 s,DataTransfer.Sales2016

you cant do a select on 3 columns of a table and UNION on 4 columns on another set of tables.

SELECT
  Sales2015.MakeName
 ,Sales2016.MakeName
 ,Sales2015.ModelName
 ,Sales2016.ModelName
 ,Sales2017.MakeName
 ,Sales2017.ModelName
FROM DataTransfer.Sales2015 
UNION 
SELECT s.MakeName, s.ModelName 
FROM DataTransfer.Sales2015 s,DataTransfer.Sales2016 
UNION 
SELECT s.MakeName, s.ModelName 
FROM DataTransfer.Sales2016 s
5

Hello Community,

I have reported the issue to the product owners at dbForge and they have suggested there might be an issue with product. They're investigating.

Thanks for your input

6

as yosiasz said.... super confusing

-- this will give blank records where not relevant but hopefully the concept will get you on the right path

SELECT
Sales2015.MakeName As Make2015
,'' as Make2016
,Sales2015.ModelName as Model2015
,'' as Model2016
,'' as Make2017
,'' as Model2017
FROM DataTransfer.Sales2015
UNION
SELECT
''
,Sales2016.MakeName
,''
,Sales2016.ModelName
,''
,''
FROM DataTransfer.Sales2016
UNION
SELECT
''
,''
,''
,''
,Sales2017.MakeName
,Sales2017.ModelName
FROM DataTransfer.Sales2017

7

Still has no clue as to what exactly it is you're trying to accomplish and no sample data to test on.
I'm taking a wild guess in beliving this might get you closer to what (I think) you want:

select b.makename as make2015
      ,c.makename as make2016
      ,b.modelname as model2015
      ,c.modelname as model2016
      ,d.makename as make2017
      ,d.modelname as model2017
  from (select makename
              ,modelname
          from datatransfer.sales2015
        union
        select makename
              ,modelname
          from datatransfer.sales2016
        union
        select makename
              ,modelname
          from datatransfer.sales2017
       ) as a
       left outer join datatransfer.sales2015 as b
                    on b.makename=a.makename
                   and b.modelname=a.modelname
       left outer join datatransfer.sales2016 as c
                    on c.makename=a.makename
                   and c.modelname=a.modelname
       left outer join datatransfer.sales2017 as d
                    on d.makename=a.makename
                   and d.modelname=a.modelname
 group by b.makename
         ,c.makename
         ,b.modelname
         ,c.modelname
         ,d.makename
         ,d.modelname
;
1 Like
8

Thanks again bitsmed... this did the trick