Link to home
Create AccountLog in
Avatar of miguel_j
miguel_jFlag for Australia

asked on

How to combine two sql queries in one result table

How would i combine the below two queries to one so that i have the results in one table

SELECT TOP (5) a.PrintPrice
FROM dbo.[Products.Printing.PrintPrice] AS a
WHERE (a.ID >= 3)
ORDER BY a.ID

SELECT TOP (5) b.MarginRange
FROM dbo.[Products.Printing.MarginRange] AS b
WHERE (b.ID >= 3)
ORDER BY b.ID
Avatar of ee_rlee
ee_rlee
Flag of Philippines image

SELECT TOP (5) a.PrintPrice
FROM dbo.[Products.Printing.PrintPrice] AS a
WHERE (a.ID >= 3)
ORDER BY a.ID
UNION ALL
SELECT TOP (5) b.MarginRange
FROM dbo.[Products.Printing.MarginRange] AS b
WHERE (b.ID >= 3)
ORDER BY b.ID
Avatar of miguel_j

ASKER

Thank you for that
(i should have made my question clearer)
How would i have the results in seperate columns?
Avatar of chapmandew
There ORDER BY clause isn't going to work with the UNION ALL statement.  Your best bet is to combine the results into a table variable...

declare @mytable TABLE(PriceVal MONEY)

INSERT INTO @mytable(PriceVal)
SELECT TOP (5) a.PrintPrice
FROM dbo.[Products.Printing.PrintPrice] AS a
WHERE (a.ID >= 3)
ORDER BY a.ID

INSERT INTO @mytable(PriceVal)
SELECT TOP (5) b.MarginRange
FROM dbo.[Products.Printing.MarginRange] AS b
WHERE (b.ID >= 3)
ORDER BY b.ID

select * from @mytable
SELECT TOP (5) a.PrintPrice, b.MarginRange
FROM dbo.[Products.Printing.PrintPrice] AS a INNER JOIN dbo.[Products.Printing.MarginRange] AS b ON (a.ID=b.ID)
WHERE (a.ID >= 3)
ORDER BY a.ID
To put them as seperate columns, you need a field to relate them to one another......such as a category, or something of that nature.  Otherwise, you'd have to do it by rank:


declare @mytable1 TABLE(IDCol INT IDENTITY(1,1), PrintPrice MONEY)
declare @mytable2 TABLE(IDCol INT IDENTITY(1,1), MarginPrice MONEY)

INSERT INTO @mytable1(PrintPrice)
SELECT TOP (5) a.PrintPrice
FROM dbo.[Products.Printing.PrintPrice] AS a
WHERE (a.ID >= 3)
ORDER BY a.ID

INSERT INTO @mytable2(MarginPrice)
SELECT TOP (5) b.MarginRange
FROM dbo.[Products.Printing.MarginRange] AS b
WHERE (b.ID >= 3)
ORDER BY b.ID

select * from @mytable1 m1 join @mytable2 m2 on m1.IDCol = m2.IDCol
That assumes that the ID values will be the same for the top 5 from each...which they may not be.
No they are not the same values..
They are referenced from a products table
ID  |  ProductID  |  UnitPriceID  |  PrintPriceID  |  MarginRangeID
0      SLR3333            89                3                        45
1      SLR3334            55                5                        34
....

so the ID columns act as a lookup table where i reference the starting row and grab the 5 rows underneath then i wish to gather the data in one table makes ome calculations and display it on the web.
0.60      1.40
0.30      1.38
0.25      1.35
0.25      1.32
0.25      1.30
What is the problem  With:
SELECT TOP (5) a.PrintPrice, a.MarginRange
FROM dbo.[Products.Printing.MarginRange] AS a
WHERE (a.ID >= 3)
ORDER BY a.ID
the problem with that query is that the orignal data is in two different tables
declare @mytable TABLE(Price SMALLMONEY, MarginVal SMALLMONEY, UnitPrice SMALLMONEY)

INSERT INTO @mytable(Price)
SELECT TOP (5) a.PrintPrice
FROM dbo.[Products.Printing.PrintPricePerQty] AS a
WHERE (a.ID >= 46)
--ORDER BY a.ID

INSERT INTO @mytable(MarginVal)
SELECT TOP (5) b.MarginRange
FROM dbo.[Products.Printing.ProfitMarginRange] AS b
WHERE (b.ID >= 3)
--ORDER BY b.ID

INSERT INTO @mytable(UnitPrice)
SELECT TOP (5) c.UnitPrice
FROM dbo.[Products.Printing.UnitPrice] AS c
WHERE (c.ID >=5)
ORDER BY c.ID

select * from @mytable
----------
0.60      NULL      NULL
0.30      NULL      NULL
0.25      NULL      NULL
0.25      NULL      NULL
0.25      NULL      NULL
NULL      1.40      NULL
NULL      1.38      NULL
NULL      1.35      NULL
NULL      1.32      NULL
NULL      1.30      NULL
NULL      NULL      4.41
NULL      NULL      4.28
NULL      NULL      4.16
NULL      NULL      3.91
NULL      NULL      3.78
-------------
Anyway of have a result set without the nulls?
Are you using 2005?
Yes 2005
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
And it did ! Youve been a great help.. All of you.