Zolf
asked on
How to UNION SQL query
Hello there,
I have these 2 sql query and I want to UNION them to gether and get as one result.Can somebody please help me
cheers
Zolf
I have these 2 sql query and I want to UNION them to gether and get as one result.Can somebody please help me
cheers
Zolf
SELECT
a.code,
a.productName,
a. BranchCode,
a.Branch,
SUM(a.AvailQty) as AvailableQty
FROM
(
SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
dbo.Tafsil.description as productName,
dbo.Branch.code as BranchCode,
dbo.Branch.name as Branch,
SUM(dbo.Batch.actualQuantity - dbo.Batch.reservedQuantity) as AvailQty
FROM
dbo.Batch
INNER JOIN
dbo.Tafsil
ON
(
dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN
dbo.Warehouse
ON
(
dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN
dbo.Branch
ON
(
dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
dbo.WarehouseSubType1
ON
(
dbo.Warehouse.warehouseSubType1Id = dbo.WarehouseSubType1.id)
INNER JOIN
dbo.Mohin
ON
(
dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
dbo.Kol
ON
(
dbo.Mohin.kolId = dbo.Kol.id)
GROUP BY
dbo.Batch.expiryDate,
dbo.Tafsil.description,
dbo.Branch.name,
dbo.WarehouseSubType1.warehouseSubTypeId,
kol.code,
Mohin.code,
Tafsil.code,
dbo.Branch.code
HAVING
SUM(dbo.Batch.actualQuantity - dbo.Batch.reservedQuantity) > 0
AND dbo.WarehouseSubType1.warehouseSubTypeId IN (1, 4)
AND dbo.Batch.expiryDate < dateadd(month,3,getdate())
) a
GROUP BY
a.code,
a.productName,
a.Branch,
a.BranchCode
SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
dbo.Tafsil.description AS ProductName,
dbo.Branch.code,
dbo.Branch.name AS Branch,
SUM(dbo.WarehouseProductQuantity.actualQuantity - dbo.WarehouseProductQuantity.reservedQuantity
) AS AvailQty
FROM
dbo.WarehouseSubType
INNER JOIN
dbo.WarehouseType
ON
(
dbo.WarehouseSubType.warehouseTypeId = dbo.WarehouseType.id)
INNER JOIN
dbo.WarehouseSubType1
ON
(
dbo.WarehouseSubType.id = dbo.WarehouseSubType1.warehouseSubTypeId)
INNER JOIN
dbo.Warehouse
ON
(
dbo.WarehouseSubType1.id = dbo.Warehouse.warehouseSubType1Id)
INNER JOIN
dbo.Branch
ON
(
dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
dbo.WarehouseProductQuantity
ON
(
dbo.Warehouse.id = dbo.WarehouseProductQuantity.warehouseId)
INNER JOIN
dbo.Tafsil
ON
(
dbo.WarehouseProductQuantity.tafsilId = dbo.Tafsil.id)
INNER JOIN
dbo.TafsilLink
ON
(
dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN
dbo.FinishProduct
ON
(
dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN
dbo.Supplier
ON
(
dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
INNER JOIN
dbo.Mohin
ON
(
dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
dbo.Kol
ON
(
dbo.Mohin.kolId = dbo.Kol.id)
WHERE
dbo.WarehouseSubType1.warehouseSubTypeId IN (1,
4)
GROUP BY
dbo.Branch.name,
dbo.Tafsil.id,
dbo.Tafsil.description ,
dbo.Branch.code,
kol.code,
Mohin.code,
Tafsil.code
note: UNION ALL instead of UNION unless you want to get a implicit DISTINCT as result of the query (including the resources needed by the dbms to perform that one)
ASKER
Thanks for your feedbacks. Can somebody help me union those two query,for some reason I am not able to do it.
how do you try to do it, and what error do you get?
ASKER
Guy Hengel:
Error I get is
09:45:14 [SELECT - 0 row(s), 0.005 secs] 1) [Error Code: 8155, SQL State: S1000] No column name was specified for column 5 of 'a'. 2) [Error Code: 8156, SQL State: S1000] The column 'code' was specified multiple times for 'sq'. 3) [Error Code: 207, SQL State: 42S22] Invalid column name 'productcode'. 4) [Error Code: 4104, SQL State: S1000] The multi-part identifier "Branch.code" could not be bound. 5) [Error Code: 4104, SQL State: S1000] The multi-part identifier "Branch.name" could not be bound.
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.005/0.000 sec [0 successful, 0 warnings, 1 errors]
Error I get is
09:45:14 [SELECT - 0 row(s), 0.005 secs] 1) [Error Code: 8155, SQL State: S1000] No column name was specified for column 5 of 'a'. 2) [Error Code: 8156, SQL State: S1000] The column 'code' was specified multiple times for 'sq'. 3) [Error Code: 207, SQL State: 42S22] Invalid column name 'productcode'. 4) [Error Code: 4104, SQL State: S1000] The multi-part identifier "Branch.code" could not be bound. 5) [Error Code: 4104, SQL State: S1000] The multi-part identifier "Branch.name" could not be bound.
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.005/0.000 sec [0 successful, 0 warnings, 1 errors]
SELECT
code,
productname,
Branch.code,
Branch.name AS Branch,
SUM(AvailQty) AS SalesQty,
SUM(AvailableQty) as AvailableQty
FROM (
SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
dbo.Tafsil.description AS ProductName,
dbo.Branch.code,
dbo.Branch.name AS Branch,
SUM(dbo.WarehouseProductQuantity.actualQuantity - dbo.WarehouseProductQuantity.reservedQuantity) AS AvailQty,
cast(null as int) as AvailableQty
FROM
dbo.WarehouseSubType
INNER JOIN
dbo.WarehouseType
ON
(
dbo.WarehouseSubType.warehouseTypeId = dbo.WarehouseType.id)
INNER JOIN
dbo.WarehouseSubType1
ON
(
dbo.WarehouseSubType.id = dbo.WarehouseSubType1.warehouseSubTypeId)
INNER JOIN
dbo.Warehouse
ON
(
dbo.WarehouseSubType1.id = dbo.Warehouse.warehouseSubType1Id)
INNER JOIN
dbo.Branch
ON
(
dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
dbo.WarehouseProductQuantity
ON
(
dbo.Warehouse.id = dbo.WarehouseProductQuantity.warehouseId)
INNER JOIN
dbo.Tafsil
ON
(
dbo.WarehouseProductQuantity.tafsilId = dbo.Tafsil.id)
INNER JOIN
dbo.TafsilLink
ON
(
dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN
dbo.FinishProduct
ON
(
dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN
dbo.Supplier
ON
(
dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
INNER JOIN
dbo.Mohin
ON
(
dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
dbo.Kol
ON
(
dbo.Mohin.kolId = dbo.Kol.id)
WHERE
dbo.WarehouseSubType1.warehouseSubTypeId IN (1,
4)
GROUP BY
dbo.Branch.name,
dbo.Tafsil.id,
dbo.Tafsil.description ,
dbo.Branch.code,
kol.code,
Mohin.code,
Tafsil.code
UNION ALL
SELECT
a.code,
a.productName,
a. BranchCode,
a.Branch,
null,
SUM(a.AvailQty) as AvailableQty
FROM
(
SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
dbo.Tafsil.description as productName,
dbo.Branch.code as BranchCode,
dbo.Branch.name as Branch,
null,
SUM(dbo.Batch.actualQuantity - dbo.Batch.reservedQuantity) as AvailQty
FROM
dbo.Batch
INNER JOIN
dbo.Tafsil
ON
(
dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN
dbo.Warehouse
ON
(
dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN
dbo.Branch
ON
(
dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
dbo.WarehouseSubType1
ON
(
dbo.Warehouse.warehouseSubType1Id = dbo.WarehouseSubType1.id)
INNER JOIN
dbo.Mohin
ON
(
dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
dbo.Kol
ON
(
dbo.Mohin.kolId = dbo.Kol.id)
GROUP BY
dbo.Batch.expiryDate,
dbo.Tafsil.description,
dbo.Branch.name,
dbo.WarehouseSubType1.warehouseSubTypeId,
kol.code,
Mohin.code,
Tafsil.code,
dbo.Branch.code
HAVING
SUM(dbo.Batch.actualQuantity - dbo.Batch.reservedQuantity) > 0
AND dbo.WarehouseSubType1.warehouseSubTypeId IN (1, 4)
AND dbo.Batch.expiryDate < dateadd(month,3,getdate())
) a
GROUP BY
a.code,
a.productName,
a.Branch,
a.BranchCode
) sq
GROUP BY productcode
, productname
, branch
Hello,
You have to give the null a column name and datatype int
convert(int,null) as SalesQty
in the second record set
Also
the Branch.code should be given alias as branchcode
You have to give the null a column name and datatype int
convert(int,null) as SalesQty
in the second record set
Also
the Branch.code should be given alias as branchcode
ASKER
Vikas Garg:
Can you please copy the full query with your changes so i know exactly as this is a big query there are lots of chances to make error
Can you please copy the full query with your changes so i know exactly as this is a big query there are lots of chances to make error
SELECT
code,
productname,
Branch.code as BranchCode,
Branch.name AS Branch,
SUM(AvailQty) AS SalesQty,
SUM(AvailableQty) as AvailableQty
FROM (
SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
dbo.Tafsil.description AS ProductName,
dbo.Branch.code,
dbo.Branch.name AS Branch,
SUM(dbo.WarehouseProductQuantity.actualQuantity - dbo.WarehouseProductQuantity.reservedQuantity) AS AvailQty,
cast(null as int) as AvailableQty
FROM
dbo.WarehouseSubType
INNER JOIN
dbo.WarehouseType
ON
(
dbo.WarehouseSubType.warehouseTypeId = dbo.WarehouseType.id)
INNER JOIN
dbo.WarehouseSubType1
ON
(
dbo.WarehouseSubType.id = dbo.WarehouseSubType1.warehouseSubTypeId)
INNER JOIN
dbo.Warehouse
ON
(
dbo.WarehouseSubType1.id = dbo.Warehouse.warehouseSubType1Id)
INNER JOIN
dbo.Branch
ON
(
dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
dbo.WarehouseProductQuantity
ON
(
dbo.Warehouse.id = dbo.WarehouseProductQuantity.warehouseId)
INNER JOIN
dbo.Tafsil
ON
(
dbo.WarehouseProductQuantity.tafsilId = dbo.Tafsil.id)
INNER JOIN
dbo.TafsilLink
ON
(
dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN
dbo.FinishProduct
ON
(
dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN
dbo.Supplier
ON
(
dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
INNER JOIN
dbo.Mohin
ON
(
dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
dbo.Kol
ON
(
dbo.Mohin.kolId = dbo.Kol.id)
WHERE
dbo.WarehouseSubType1.warehouseSubTypeId IN (1,
4)
GROUP BY
dbo.Branch.name,
dbo.Tafsil.id,
dbo.Tafsil.description ,
dbo.Branch.code,
kol.code,
Mohin.code,
Tafsil.code
UNION ALL
SELECT
a.code,
a.productName,
a.BranchCode,
a.Branch,
CONVERT(INT,null) AS SalesQty,
SUM(a.AvailQty) as AvailableQty
FROM
(
SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
dbo.Tafsil.description as productName,
dbo.Branch.code as BranchCode,
dbo.Branch.name as Branch,
null,
SUM(dbo.Batch.actualQuantity - dbo.Batch.reservedQuantity) as AvailQty
FROM
dbo.Batch
INNER JOIN
dbo.Tafsil
ON
(
dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN
dbo.Warehouse
ON
(
dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN
dbo.Branch
ON
(
dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
dbo.WarehouseSubType1
ON
(
dbo.Warehouse.warehouseSubType1Id = dbo.WarehouseSubType1.id)
INNER JOIN
dbo.Mohin
ON
(
dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
dbo.Kol
ON
(
dbo.Mohin.kolId = dbo.Kol.id)
GROUP BY
dbo.Batch.expiryDate,
dbo.Tafsil.description,
dbo.Branch.name,
dbo.WarehouseSubType1.warehouseSubTypeId,
kol.code,
Mohin.code,
Tafsil.code,
dbo.Branch.code
HAVING
SUM(dbo.Batch.actualQuantity - dbo.Batch.reservedQuantity) > 0
AND dbo.WarehouseSubType1.warehouseSubTypeId IN (1, 4)
AND dbo.Batch.expiryDate < dateadd(month,3,getdate())
) a
GROUP BY
a.code,
a.productName,
a.Branch,
a.BranchCode
) sq
GROUP BY productcode
, productname
, branch
ASKER
No luck, I still get error
1:06:16 [SELECT - 0 row(s), 0.000 secs] 1) [Error Code: 8155, SQL State: S1000] No column name was specified for column 5 of 'a'. 2) [Error Code: 8156, SQL State: S1000] The column 'code' was specified multiple times for 'sq'. 3) [Error Code: 207, SQL State: 42S22] Invalid column name 'productcode'. 4) [Error Code: 4104, SQL State: S1000] The multi-part identifier "Branch.code" could not be bound. 5) [Error Code: 4104, SQL State: S1000] The multi-part identifier "Branch.name" could not be bound.
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]
1:06:16 [SELECT - 0 row(s), 0.000 secs] 1) [Error Code: 8155, SQL State: S1000] No column name was specified for column 5 of 'a'. 2) [Error Code: 8156, SQL State: S1000] The column 'code' was specified multiple times for 'sq'. 3) [Error Code: 207, SQL State: 42S22] Invalid column name 'productcode'. 4) [Error Code: 4104, SQL State: S1000] The multi-part identifier "Branch.code" could not be bound. 5) [Error Code: 4104, SQL State: S1000] The multi-part identifier "Branch.name" could not be bound.
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]
Can you try this
SELECT
code,
productname,
Branchcode,
Branch.name AS Branch,
SUM(AvailQty) AS SalesQty,
SUM(AvailableQty) as AvailableQty
FROM (
SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
dbo.Tafsil.description AS ProductName,
dbo.Branch.code AS Branchcode,
dbo.Branch.name AS Branch,
SUM(dbo.WarehouseProductQuantity.actualQuantity - dbo.WarehouseProductQuantity.reservedQuantity) AS AvailQty,
cast(null as int) as AvailableQty
FROM
dbo.WarehouseSubType
INNER JOIN
dbo.WarehouseType
ON
(
dbo.WarehouseSubType.warehouseTypeId = dbo.WarehouseType.id)
INNER JOIN
dbo.WarehouseSubType1
ON
(
dbo.WarehouseSubType.id = dbo.WarehouseSubType1.warehouseSubTypeId)
INNER JOIN
dbo.Warehouse
ON
(
dbo.WarehouseSubType1.id = dbo.Warehouse.warehouseSubType1Id)
INNER JOIN
dbo.Branch
ON
(
dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
dbo.WarehouseProductQuantity
ON
(
dbo.Warehouse.id = dbo.WarehouseProductQuantity.warehouseId)
INNER JOIN
dbo.Tafsil
ON
(
dbo.WarehouseProductQuantity.tafsilId = dbo.Tafsil.id)
INNER JOIN
dbo.TafsilLink
ON
(
dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN
dbo.FinishProduct
ON
(
dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN
dbo.Supplier
ON
(
dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
INNER JOIN
dbo.Mohin
ON
(
dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
dbo.Kol
ON
(
dbo.Mohin.kolId = dbo.Kol.id)
WHERE
dbo.WarehouseSubType1.warehouseSubTypeId IN (1,
4)
GROUP BY
dbo.Branch.name,
dbo.Tafsil.id,
dbo.Tafsil.description ,
dbo.Branch.code,
kol.code,
Mohin.code,
Tafsil.code
UNION ALL
SELECT
a.code,
a.productName,
a. BranchCode,
a.Branch,
CONVERT(INT,null) AvailQty,
SUM(a.AvailQty) as AvailableQty
FROM
(
SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
dbo.Tafsil.description as productName,
dbo.Branch.code as BranchCode,
dbo.Branch.name as Branch,
null,
SUM(dbo.Batch.actualQuantity - dbo.Batch.reservedQuantity) as AvailQty
FROM
dbo.Batch
INNER JOIN
dbo.Tafsil
ON
(
dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN
dbo.Warehouse
ON
(
dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN
dbo.Branch
ON
(
dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
dbo.WarehouseSubType1
ON
(
dbo.Warehouse.warehouseSubType1Id = dbo.WarehouseSubType1.id)
INNER JOIN
dbo.Mohin
ON
(
dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
dbo.Kol
ON
(
dbo.Mohin.kolId = dbo.Kol.id)
GROUP BY
dbo.Batch.expiryDate,
dbo.Tafsil.description,
dbo.Branch.name,
dbo.WarehouseSubType1.warehouseSubTypeId,
kol.code,
Mohin.code,
Tafsil.code,
dbo.Branch.code
HAVING
SUM(dbo.Batch.actualQuantity - dbo.Batch.reservedQuantity) > 0
AND dbo.WarehouseSubType1.warehouseSubTypeId IN (1, 4)
AND dbo.Batch.expiryDate < dateadd(month,3,getdate())
) a
GROUP BY
a.code,
a.productName,
a.Branch,
a.BranchCode
) sq
GROUP BY code
, productname
, branch
ASKER
Now I get another error
No column name was specified for column 5 of 'a'. 2) [Error Code: 4104, SQL State: S1000] The multi-part identifier "Branch.name" could not be bound.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help!! But now I get another error
Column 'sq.Branchcode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. 2) [Error Code: 4104, SQL State: S1000] The multi-part identifier "Branch.name" could not be bound.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks guys!!
To apply union to two recordset
you have to have same data type to each column in both query
select col1 from tabl1
union
select col2 from tabl2
Number of columns and datatype should be same