SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Two Queries one table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 01/23/2013 :  09:34:26  Show Profile  Reply with Quote
I am trying to do two queries and have the data end up in one table. Here is what I have so far.
USE [MT]
GO
/****** Object:  StoredProcedure [dbo].[PMPartsDue]    Script Date: 01/23/2013 08:10:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[AllPartsDue]
(
@zWODue Integer
)
AS
BEGIN
SET NOCOUNT ON
SELECT     sparts.QtyonHand, sparts.PointofReorder, sparts.OrderedAmnt, spartven.PartNum, spartven.Component, spartven.Price, spartven.PrimaryVendor, 
                      sparts.ComponentID, pmaint.TaskNum1, pmaint.TaskNum, pmaint.EquipmentID, pmparts.QtyNeeded
INTO		partsreorder
FROM         pmparts INNER JOIN
                      pmaint INNER JOIN
                      pmtask ON pmaint.TaskNum = pmtask.TaskNum ON pmparts.TaskNum = pmaint.TaskNum INNER JOIN
                      spartven INNER JOIN
                      sparts ON spartven.ComponentID = sparts.ComponentID ON pmparts.ComponentNum = sparts.ComponentID
WHERE     (pmtask.NextPerfDate <= DATEADD(dd, @zWODue, DATEDIFF(dd, 0, GETDATE()))) AND (pmtask.WONum IS NULL) AND (pmaint.PMStatus = 'Active') AND 
                      (spartven.PrimaryVendor = 'True')
UNION
SELECT     sparts.ComponentID, sparts.QtyonHand, sparts.PointofReorder, sparts.OrderedAmnt, spartven.PartNum, spartven.VendorID, spartven.Price, 
                      spartven.Component
FROM         sparts INNER JOIN
                      spartven ON sparts.ComponentID = spartven.ComponentID
WHERE     (spartven.PrimaryVendor = 'True') AND (sparts.QtyonHand <= sparts.PointofReorder)
SELECT * 
FROM partsreorder
END


My problem is in one table I am returning more fields that in the other but all the fields are the same except for in the second query the extra fields (that are not being returned in this query) would just be blank. Is there a way to do that?
I hope that makes sense.
Thanks,
Stacy

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/23/2013 :  09:48:08  Show Profile  Reply with Quote
Create the partsreorder table first, and then insert into it. Also, when you do a union query, the top and bottom queries have to have the same number of fields (as you know!) and they
need to be in the same order. Just use nulls or some default value where the columns differ

INSERT INTO table (a,b,c,d,e)
Select a,b,c,d,e from table1
union
select a,null,null,d,e from table2

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

StacyOW
Yak Posting Veteran

USA
87 Posts

Posted - 01/23/2013 :  14:11:55  Show Profile  Reply with Quote
quote:
Originally posted by jimf

Create the partsreorder table first, and then insert into it. Also, when you do a union query, the top and bottom queries have to have the same number of fields (as you know!) and they
need to be in the same order. Just use nulls or some default value where the columns differ

INSERT INTO table (a,b,c,d,e)
Select a,b,c,d,e from table1
union
select a,null,null,d,e from table2

Jim

Everyday I learn something that somebody else already knew


Thanks Jim for your reply. This is what I ended up with.
USE [MT]
GO
/****** Object:  StoredProcedure [dbo].[PMPartsDue]    Script Date: 01/23/2013 08:10:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[AllPartsDue]
(
@zWODue Integer
)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO partsreorder (QtyonHand, PointofReorder, OrderedAmnt, PartNum, Component, Price, PrimaryVendor, 
                      ComponentID, TaskNum1, TaskNum, EquipmentID, QtyNeeded)
SELECT     sparts.QtyonHand, sparts.PointofReorder, sparts.OrderedAmnt, spartven.PartNum, spartven.Component, spartven.Price, spartven.PrimaryVendor, 
                      sparts.ComponentID, pmaint.TaskNum1, pmaint.TaskNum, pmaint.EquipmentID, pmparts.QtyNeeded
FROM         pmparts INNER JOIN
                      pmaint INNER JOIN
                      pmtask ON pmaint.TaskNum = pmtask.TaskNum ON pmparts.TaskNum = pmaint.TaskNum INNER JOIN
                      spartven INNER JOIN
                      sparts ON spartven.ComponentID = sparts.ComponentID ON pmparts.ComponentNum = sparts.ComponentID
WHERE     (pmtask.NextPerfDate <= DATEADD(dd, @zWODue, DATEDIFF(dd, 0, GETDATE()))) AND (pmtask.WONum IS NULL) AND (pmaint.PMStatus = 'Active') AND 
                      (spartven.PrimaryVendor = 'True')
UNION
SELECT     sparts.QtyonHand, sparts.PointofReorder, sparts.OrderedAmnt, spartven.PartNum, spartven.Component, spartven.Price, spartven.PrimaryVendor, 
                      spartven.ComponentID, NULL, NULL, NULL, NULL
FROM         sparts INNER JOIN
                      spartven ON sparts.ComponentID = spartven.ComponentID
WHERE     (spartven.PrimaryVendor = 'True') AND (sparts.QtyonHand <= sparts.PointofReorder)
SELECT * 
FROM partsreorder
END

It seems to be working fine except that if both queries are returning the same component# then I am getting both in the table which causes a constraint. Not sure exactly how to handle this. Is there a way when I do the 2nd query to check if any of the comp#'s are already in that partsreorder table?
Thanks for you help.
Stacy
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/24/2013 :  07:20:42  Show Profile  Reply with Quote
You can't check the 2 part to see if it's already in the table as part of a union query. You'd have to separate it out and make it it's own insert -- this shouldn't affect performance at all.

INSERT INTO partsreorder
SELECT <STUFF>
FROM <tables>
WHERE NOT EXISTS (select * from partreorder po where po.Component = <tables>.component

Jim




Everyday I learn something that somebody else already knew
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/25/2013 :  01:29:30  Show Profile  Reply with Quote
for that isnt it enough to add NOT EXISTS conditions based on pmparts and partsreorder table to look for components if you want to do it inline in same UNION query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000