SQLTeam.com | Weblogs | Forums

Please help join two SELECT queries into one table

Transact-SQL
#1

Hi,
I've only started writing in SQL recently and I was trying to join the two queries I made below into one table

image

-- Outbounds Part 1
SELECT
WWL.Barcode AS "Location",
COUNT(DISTINCT WWA.PalletId) AS "#Pallets Due"

FROM
Warehouse.WarehouseNotes AS WWN
INNER JOIN Warehouse.WarehouseActions   WWA ON WWA.WarehouseNoteId = WWN.Id
INNER JOIN Warehouse.WarehouseLocations   WWL ON WWA.RequestedLocationId = WWL.Id

WHERE
(WWL.Barcode LIKE '%BU%' OR WWL.Barcode LIKE '%LB%') AND WWA.PalletId IS NOT NULL
AND (WWN.NoteType = 1 AND (WWA.ActionStatus = 1 OR WWA.ActionStatus = 2))

GROUP BY
WWL.Barcode
 
ORDER BY
"Location"

-- Outbounds Part 2
SELECT
WWL.Barcode AS "Location",
COUNT(DISTINCT WWA.PalletId) AS "#Pallets Current"

FROM
Warehouse.WarehouseNotes AS WWN
INNER JOIN Warehouse.WarehouseActions   WWA ON WWA.WarehouseNoteId = WWN.Id
INNER JOIN Warehouse.WarehouseLocations   WWL ON WWA.RequestedLocationId = WWL.Id

WHERE
(WWL.Barcode LIKE '%BU%' OR WWL.Barcode LIKE '%LB%') AND WWA.PalletId IS NOT NULL
AND (
(WWN.NoteType = 6 AND (WWN.NoteStatus = 1 OR WWN.NoteStatus = 2)) OR (WWN.NoteType = 1 AND(WWN.NoteStatus = 2 AND WWA.ActionStatus = 3))
)

GROUP BY
WWL.Barcode
 
ORDER BY
"Location"

Any help would be greatly appreciated!

#2

query 1

join

query 2 on barcode

select * from 
  ( select * from tableA ) a 
  join 
  ( select * from tableB ) b 
on a.barcode = b.barcode
#3 
SELECT
WWL.Barcode AS "Location",
COUNT(DISTINCT WWA.PalletId) AS "#Pallets Due"

FROM
Warehouse.WarehouseNotes AS WWN
INNER JOIN Warehouse.WarehouseActions   WWA ON WWA.WarehouseNoteId = WWN.Id
INNER JOIN Warehouse.WarehouseLocations   WWL ON WWA.RequestedLocationId = WWL.Id

WHERE
(WWL.Barcode LIKE '%BU%' OR WWL.Barcode LIKE '%LB%') AND WWA.PalletId IS NOT NULL
AND ((WWN.NoteType = 1 AND (WWA.ActionStatus = 1 OR WWA.ActionStatus = 2)) OR
    ((WWN.NoteType = 6 AND (WWN.NoteStatus = 1 OR WWN.NoteStatus = 2)) OR (WWN.NoteType = 1 AND (WWN.NoteStatus = 2 AND WWA.ActionStatus = 3)))     

GROUP BY
WWL.Barcode
 
ORDER BY
"Location"
#4

Thanks harishgg1,
I've been able to implement your suggest to the below

-- Outbounds [CPU time = 3140 ms,  elapsed time = 1212 ms]

SELECT 
CASE 
	WHEN a.Location IS NULL THEN b.Location
	ELSE a.Location
END AS "Location",
a.[#Pallets Current],
b.[#Pallets Due]

FROM
(
	SELECT
	WWL.Barcode AS "Location",
	COUNT(DISTINCT WWA.PalletId) AS "#Pallets Current"

	FROM
	Warehouse.WarehouseNotes AS WWN
	INNER JOIN Warehouse.WarehouseActions   WWA ON WWA.WarehouseNoteId = WWN.Id
	INNER JOIN Warehouse.WarehouseLocations   WWL ON WWA.RequestedLocationId = WWL.Id

	WHERE
	(WWL.Barcode LIKE '%BU%' OR WWL.Barcode LIKE '%LB%') AND WWA.PalletId IS NOT NULL
	AND (
	(WWN.NoteType = 6 AND (WWN.NoteStatus = 1 OR WWN.NoteStatus = 2)) OR (WWN.NoteType = 1 AND(WWN.NoteStatus = 2 AND WWA.ActionStatus = 3))
	)

	GROUP BY
	WWL.Barcode
) a 

FULL OUTER JOIN

(
	SELECT
	WWL.Barcode AS "Location",
	COUNT(DISTINCT WWA.PalletId) AS "#Pallets Due"

	FROM
	Warehouse.WarehouseNotes AS WWN
	INNER JOIN Warehouse.WarehouseActions   WWA ON WWA.WarehouseNoteId = WWN.Id
	INNER JOIN Warehouse.WarehouseLocations   WWL ON WWA.RequestedLocationId = WWL.Id

	WHERE
	(WWL.Barcode LIKE '%BU%' OR WWL.Barcode LIKE '%LB%') AND WWA.PalletId IS NOT NULL
	AND (WWN.NoteType = 1 AND (WWA.ActionStatus = 1 OR WWA.ActionStatus = 2))

	GROUP BY
	WWL.Barcode
) b 

ON a.Location = b.Location

ORDER BY a.Location