How to? - SQL Server Forums

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)

How to?

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-12-03 : 20:20:49

Good day...

I had this sample datas...



SELECT tblStocks.* 
FROM (
	SELECT 1 as stockID, 'aaa' as stockName union all
	SELECT 2 as stockID, 'bbb' as stockName union all
	SELECT 3 as stockID, 'ccc' as stockName 
	) as tblStocks

and this...



SELECT tblTrans.*
FROM (	
	SELECT '2006/12/1' as transDate, 1 as stockID, 5 as transQTY union all
	SELECT '2006/12/2' as transDate, 1 as stockID, 5 as transQTY
	) as tblTrans

Result should look like this...



stockID	   stockName	transDate	transQTY	
--------------------------------------------------------
1	   aaa		2006/12/1	5
2	   bbb		2006/12/1       null
3	   ccc		2006/12/1	null
1          aaa		2006/12/2	5
2	   bbb		2006/12/2       null
3	   ccc		2006/12/2	null

tnx

Want Philippines to become 1st World COuntry? Go for World War 3...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-03 : 23:58:37

You question doesn't make sense!

You want to join the two derived tables and still want to bring additional rows for StockID 2,3 magically out of air?

SELECT S.STOCKID, S.STOCKNAME, T.TRANSDATE, T.TRANSQTY
FROM
(
	SELECT 1 as stockID, 'aaa' as stockName union all
	SELECT 2 as stockID, 'bbb' as stockName union all
	SELECT 3 as stockID, 'ccc' as stockName 
	) as S
LEFT JOIN
(	
	SELECT '2006/12/1' as transDate, 1 as stockID, 5 as transQTY union all
	SELECT '2006/12/2' as transDate, 1 as stockID, 5 as transQTY
	) as T
on
S.STOCKID = T.STOCKID

Harsh Athalye
India.
"Nothing is Impossible"

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-12-04 : 00:09:31

quote:
Originally posted by harsh_athalye

You question doesn't make sense!

You want to join the two derived tables and still want to bring additional rows for StockID 2,3 magically out of air?

SELECT S.STOCKID, S.STOCKNAME, T.TRANSDATE, T.TRANSQTY
FROM
(
SELECT 1 as stockID, 'aaa' as stockName union all
SELECT 2 as stockID, 'bbb' as stockName union all
SELECT 3 as stockID, 'ccc' as stockName
) as S
LEFT JOIN
(
SELECT '2006/12/1' as transDate, 1 as stockID, 5 as transQTY union all
SELECT '2006/12/2' as transDate, 1 as stockID, 5 as transQTY
) as T
on
S.STOCKID = T.STOCKID

Harsh Athalye
India.
"Nothing is Impossible"

Why did you say it doesn't make sense? Sorry but your way of approach sounds rude to me.

YES Harsh! Its for a purpose my friend... I will used that result to pivot the datas by dates. By the way i already got it! tnx for the reply.


SELECT tblFixed.*, tblNewTrans.transQTY
FROM (
	SELECT tblStocks.*, tblTrans.transDate
	FROM (
		SELECT 1 as stockID, 'aaa' as stockName union all
		SELECT 2 as stockID, 'bbb' as stockName union all
		SELECT 3 as stockID, 'ccc' as stockName 
		) as tblStocks
	LEFT JOIN (
			SELECT '2006/12/1' as transDate, 1 as stockID, 5 as transQTY union all
			SELECT '2006/12/2' as transDate, 1 as stockID, 5 as transQTY
		      ) as tblTrans ON 1=1
	) as tblFixed	
LEFT JOIN 	(
		SELECT '2006/12/1' as transDate, 1 as stockID, 5 as transQTY union all
		SELECT '2006/12/2' as transDate, 1 as stockID, 5 as transQTY	
		) as tblNewTrans ON tblNewTrans.transDate = tblFixed.transDate and
				          tblNewTrans.stockID = tblFixed.stockID
ORDER BY tblFixed.transDate, tblFixed.stockID

Want Philippines to become 1st World COuntry? Go for World War 3...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources