Nested SQL - 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 2008 Forums

Transact-SQL (2008)

Nested SQL

Author

Topic

rico1931
Starting Member

37 Posts

Posted - 2009-05-26 : 15:36:50

Hello all,

I am stuck on this problem for the last week with no improvement. I want to try to create a nested SQL statement. This is my code


SELECT DISTINCT  *
FROM dbo.PAGEFACTS 
INNER JOIN dbo.PAGEKEYS ON dbo.PAGEFACTS.PK_ID = dbo.PAGEKEYS.PK_ID 
INNER JOIN dbo.FACTKEYS ON PAGEKEYS.FACT_PAGEKEY_PKID = dbo.FACTKEYS.FACT_PAGEKEY_PKID 
INNER JOIN dbo.FACTS ON dbo.FACTKEYS.FACT_KEY = dbo.FACTS.FACT_KEY 
INNER JOIN dbo.STYLES ON dbo.FACTS.FACT_STYLE_ID = dbo.STYLES.StyleID 
LEFT OUTER JOIN dbo.GRAPHICS ON dbo.FACTS.FACT_GRA_ALIAS = dbo.GRAPHICS.GRA_ALIAS
WHERE dbo.FACTS.FACT_KEY = parm 
AND dbo.PAGEFACTS.PF_DIV_REGION = dbo.FACTS.FACT_REGION 
AND dbo.PAGEFACTS.PK_ID = bk
ORDER BY dbo.PAGEFACTS.PF_DIV_REGION

in the WHERE statement parm and bk are variables from the URL to get the required data from the tables. Now what I need is something that will take each REGION (LEFT, MAIN) and only populate FACTS that are associated with each region LEFT, MAIN. I tried doing this in dreamweaver but with no luck so I think its the SQL.

The other approach I tried doing is by doing this in 2 tables. First by populating the regions and then checking for FACTS then when .EOF move to the next region and populating the content etc. But still nothing. I don't know how to do this dynamically. Any help would be great

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-26 : 16:22:25

Some sample data and expected output would be excellent.
Maybe all you need is a recursive CTE?

E 12°55'05.63"
N 56°04'39.26"

rico1931
Starting Member

37 Posts

Posted - 2009-05-27 : 09:53:59

well the sample data is long but here it is

PF_DIV_REGION	PF_SEQ	PF_WIDTH	PF_PKGNDIMG_ALIAS	FACT_PAGEKEY_PKID	PK_FACT_KEY		FACT_KEY  FACT_PAGEKEY_PKID	FACT_ID	FACT_KEY     FACT_REGION	
LEFT		10	24.0%		gradient1.gif		DM_L(sm)_M(lg)_R(sm)	DM_L(sm)_M(lg)_R(sm)	550	DM_L(sm)_M(lg)_R(sm)		550	     LEFT	
LEFT		10	24.0%		gradient1.gif		DM_L(sm)_M(lg)_R(sm)	DM_L(sm)_M(lg)_R(sm)	550	DM_L(sm)_M(lg)_R(sm)		550	     LEFT	
MIDDLE		20	49.9%		red_grad_C.gif		DM_L(sm)_M(lg)_R(sm)	DM_L(sm)_M(lg)_R(sm)	550	DM_L(sm)_M(lg)_R(sm)		550	     MIDDLE	
MIDDLE		20	49.9%		red_grad_C.gif		DM_L(sm)_M(lg)_R(sm)	DM_L(sm)_M(lg)_R(sm)	550	DM_L(sm)_M(lg)_R(sm)		550	     MIDDLE	
RIGHT		30	24.0%		yellow_grad.gif		DM_L(sm)_M(lg)_R(sm)	DM_L(sm)_M(lg)_R(sm)	550	DM_L(sm)_M(lg)_R(sm)		550	     RIGHT	
RIGHT		30	24.0%		yellow_grad.gif		DM_L(sm)_M(lg)_R(sm)	DM_L(sm)_M(lg)_R(sm)	550	DM_L(sm)_M(lg)_R(sm)		550	     RIGHT

Sorry i'm still new at this and don't know how to copy the results correctly. But the output on the page should loop. So at first in the FACT_REGION it would know to do the two results with FACT_REGION = LEFT then do the MIDDLE and last would be RIGHT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources