Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Two Queries one table

Author  Topic 

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2013-01-23 : 09:34:26
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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-23 : 09:48:08
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

93 Posts

Posted - 2013-01-23 : 14:11:55
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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-24 : 07:20:42
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

52326 Posts

Posted - 2013-01-25 : 01:29:30
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
   

- Advertisement -