Results from 2 queries returned as a single row

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 2000 Forums

Transact-SQL (2000)

Results from 2 queries returned as a single row

Author

Topic

liffey
Yak Posting Veteran

58 Posts

Posted - 2004-06-03 : 10:35:28

I need the results from 2 queries returned as a single row. How can I achieve this. The result from the first query will ALWAYS return 1 row and the second query will ALWAYS return 2 rows.

I want a single row returned showing data from both queries.

As a sample of what I am trying to achieve I show 2 queries below using northwind.

use northwind
select productname, categoryid from products where productid=1

select productid from [order details] where orderid=10249

Required result :

productname, categoryid, p_id_1, p_id_2

any help appreciated.

Declan

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-03 : 10:56:06

Lookup JOIN in Books Online. You can join the tables in your query to bring back exactly the results requested.

liffey
Yak Posting Veteran

58 Posts

Posted - 2004-06-03 : 12:34:24

I think you have missed my point.

SELECT Products.ProductName, Products.CategoryID, [Order Details].ProductID
FROM Products CROSS JOIN
[Order Details]
WHERE (Products.ProductID = 1) AND ([Order Details].OrderID = 10249)

yeilds

Chai, 1, 14
Chai, 1, 51

What I want is

Chai, 1, 14, 51

in 1 row.

liffey
Yak Posting Veteran

58 Posts

Posted - 2004-06-03 : 12:57:40

I guess what I want is a PIVOT but can not figure out how to do it.

ie select a field from a table, resulting in 2 rows, and pivot this to return a single row with 2 columns!

vito1281
Starting Member

12 Posts

Posted - 2004-06-05 : 15:25:34

I think something like this may work for you:


SELECT	p.ProductName, p.CategoryID, od.ProductID, od2.ProductID
FROM	Products p 
INNER JOIN
	(SELECT min(ProductID) as ProductID FROM [Order Details] WHERE OrderID = 10249) od ON p.ProductID = od.ProductID
INNER JOIN
	(SELECT max(ProductID) as ProductID FROM [Order Details] WHERE OrderID = 10249) od2 ON p.ProductID = od2.ProductID
WHERE p.ProductID = 1

This, of course, assumes that you always get 2 records from Order Details.

liffey
Yak Posting Veteran

58 Posts

Posted - 2004-06-05 : 16:40:24

Vito,

Thanks for the pointer. Problem solved.

Declan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources