Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 156
  • Last Modified:

UNION Where not in MySQL

I am trying to union 2 queries where 1 is not in the other for example
                 SELECT ToolType,PartNumber,Quantity,InsertDate,InventoryListID,LocationID FROM TEMP_PARTS Where InsertDate BETWEEN   
                '2012-01-01 12:00:00 AM' AND '2014-12-13 11:59:59 PM'
AND UNION ALL
//////////////WHERE partnumber not in the above query//////////////////////////
SELECT il.PartDesc as 'ToolType',il.PartNumber,ip.Quantity,CAST('2012-01-01 12:00:00 AM' AS DateTime) as 'InsertDate',il.UID as 'InventoryListID',ip.LocationID FROM
Inventory_Parts ip
JOIN Inventory_InventoryList as il ON il.UID = InventoryListID 
 WHERE ip.LocationID = 1 and il.isVisible = 1 

Open in new window

How do I do that?
0
r3nder
Asked:
r3nder
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you mean:
 SELECT ToolType,PartNumber,Quantity,InsertDate,InventoryListID,LocationID 
FROM TEMP_PARTS 
Where InsertDate BETWEEN   '2012-01-01 12:00:00 AM' AND '2014-12-13 11:59:59 PM'
UNION ALL
SELECT il.PartDesc as 'ToolType',il.PartNumber,ip.Quantity,CAST('2012-01-01 12:00:00 AM' AS DateTime) as 'InsertDate',il.UID as 'InventoryListID',ip.LocationID 
FROM Inventory_Parts ip
JOIN Inventory_InventoryList as il 
ON il.UID = InventoryListID 
 WHERE ip.LocationID = 1 and il.isVisible = 1 
 AND NOT EXISTS( SELECT null
FROM TEMP_PARTS tp
Where tp.InsertDate BETWEEN   '2012-01-01 12:00:00 AM' AND '2014-12-13 11:59:59 PM'
  AND tp.partnumber  = il.partnumber  ) 

Open in new window

0
 
r3nderAuthor Commented:
Hell yeah! that is what I mean thank you Sir
0
 
r3nderAuthor Commented:
Thank you Guy
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now