Create View with 2 Select Queries

Hi,

I don't even know if this is possible, but here goes.

I have 2 select queries.

1) SELECT w.Item AS [WareHouse Item], w.Quantity AS [WareHouse Quantity],

w.RestockLevel AS [WareHouse Restock], w.ReorderPoint AS [WareHouse ReorderPoint],

SUM(Wp.QuantityOrdered - Wp.QuantityReceivedToDate) AS WareHouseOnOrder

FROM WH.dbo.PurchaseOrderEntry AS wp INNER JOIN

WH.dbo.Item AS w ON wp.ItemID = w.ID INNER JOIN

WH.dbo.PurchaseOrder AS Wpo ON Wp.PurchaseOrderID = WPO.ID

Where (wPO.POType < 2) AND (wPO.Status = '0')

Group By w.Item, w.Quantity, w.RestockLevel, w.ReorderPoint

Order By w.Item

My socond query is the same, but from a different database on the same Server. If I join the 2 queries I get duplicate Items in my results, because of the 2 databases.

My question is:

Can I run both queries to create one View so that I can create my Crystal report from that?

Any better Ideas would be appretiated!

Thanks

what are you joining the two queries on?|||

You can use UNION. (If the column structure and order is the same.)

SELECT ... FROM dbo.MyTable WHERE ...

UNION

SELECT ... FROM MyOtherDatabase.dbo.MyTable WHERE ...

|||

Arnie Rowland wrote:

You can use UNION. (If the column structure and order is the same.)

SELECT ... FROM dbo.MyTable WHERE ...

UNION

SELECT ... FROM MyOtherDatabase.dbo.MyTable WHERE ...

or "UNION ALL" if you want the duplicates.

|||

Thanks Arnie and Michael

That was exactly what I needed.

Thanks!!!

|||

Hi,

I am not sure if view is your requirement or not. Aa far as the data from query to a report is concerned you can you use a temp table in stored procedure to do that or use UNION to combine these results.

Thanks,

Paraclete

|||

That is also VERY true.

After seeing that I can actually get all the information using the union that Arnie suggested I will probably create a stored procedure to run my select Statement.

Thanks Again!!