Suggest the query... - 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

General SQL Server Forums

New to SQL Server Programming

Suggest the query...

Author

Topic

vishveshk
Starting Member

3 Posts

Posted - 2010-05-05 : 09:36:54

I have 4 tables with shares column,funmanager,ticker.

I need to get the data as

ticker funmanager table1shares table2shares table3shares

sample data is like

tabl1

ticker funmanager table1shares

IBM Oldmutal 20000

IBM NEWHOLD 22000

TABLE2

Ticker funmanager Table2shares

IBM Oldmutual 30000

IBM NEWHOLD 18000

Table3

Ticker funmanager table3shares

IBM OLDMUTUAL 5000

IBM NEWHOLD 3000

in final table

ticker funmanager table1shares table2shares table3shares

IBM OLDmutual 20000 30000 5000

IBM NEWHOLD 22000 18000 3000

i am trying using group by but it is not working ...please suggest the query...

vishvehskk.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-05 : 10:08:47

[code]SELECT t1.ticker, t1.funmanager, t1.table1shares, t2.tableshares, t3.tableshares
FROM table_1 t1
INNER JOIN table_2 t2 ON t1.ticker = t2.ticker AND t1.funmanager = t2.funmanager
INNER JOIN table_3 t3 ON t2.ticker = t3.ticker AND t2.funmanager = t3.funmanager[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-05 : 14:45:07

will you've corresponding records in all three tables always? if not, you might have to change INNER to LEFT JOIN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vishveshk
Starting Member

3 Posts

Posted - 2010-05-06 : 02:43:37

I tried this but it is giving duplicate and mutliple records...i work out the same query but it is not working....Thats y i planned for a IS PACKAGE so that i dont get duplicates and multiple reocords...

vishvehskk.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-06 : 13:12:32

it may be that you've one to many relation existing between tables.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-06 : 14:23:01

Maybe this?

SELECT 
	Ticker,
	funmanager,
	MAX(table1shares) AS table1shares,
	MAX(Table2shares) AS Table2shares,
	MAX(table3shares) AS table3shares
FROM
	(
		SELECT 
			ticker,
			funmanager,
			table1shares,
			NULL AS Table2shares,
			NULL AS table3shares
		FROM 
			tabl1
			
		UNION ALL
		
		SELECT 
			ticker,
			funmanager,
			NULL,
			Table2shares,
			NULL ,
		FROM 
			table2
			
		UNION ALL
		
		SELECT 
			ticker,
			funmanager,
			NULL,
			NULL,
			table3shares
		FROM 
			table3	
	) AS T
GROUP BY
	Ticker,
	funmanager

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources