HOW to have indexed view of 2 tables ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

HOW to have indexed view of 2 tables ?

Author

Topic

Vaishu
Posting Yak Master

178 Posts

Posted - 2011-02-22 : 11:22:03

Help me to get ALL ROWS FROM TblconPRc and unmatched rows from TblDEFPRc. So the result will be like below. PLIST and ITEMCODE have same data on both table.

NOTE: The whole idea is if the customer have price available in TblconPRc then that price should show up, else the price from TblDEFPRc. Also I need to INDEX this view, so not able to use UNION operator. Any help getting around this to create INDEXED VIEW

EXPECTED RESULT IS :
PLIST----ITEMCODE----CALPRICE
SAM-------123WER------1.25
TAS-------FUJ345------1.78
SAM-------FUJ345------1.45
MAN-------123WER------1.35 -------Top 4 rows from TblconPRc---
TAS-------SON567------4.55 -------below 2 rows from TblDEFPRc---
COL-------123WER------1.55

TblconPRc
PLIST----ITEMCODE----FPRICE
SAM-------123WER------1.25
TAS-------FUJ345------1.78
SAM-------FUJ345------1.45
MAN-------123WER------1.35

TblDEFPRc
PLIST----ITEMCODE----UNITPRC
TAS-------SON567------4.55 - unmatched row
COL-------123WER------1.55 - unmatched row
SAM-------123WER------1.55 -match with TblconPRc by PLIST & ITEMCODE
TAS-------FUJ345------1.98 -match with TblconPRc by PLIST & ITEMCODE
SAM-------FUJ345------1.65 -match with TblconPRc by PLIST & ITEMCODE
MAN-------123WER------1.75 --match with TblconPRc by PLIST & ITEMCODE

I have got the answer in this forum as below and its works BUT IT USE UNION. ANY IDEA TO GET SAME RESULT WITHOUT USING UNION
;with cte as
(
select PLIST, ITEMCODE, FPRICE
from #TblconPRc

union all

select t2.PLIST,t2.ITEMCODE,t2.UNITPRC
from #TblconPRc t1
right join #TblDEFPRc t2 on t1.PLIST=t2.PLIST and t1.ITEMCODE=t2.ITEMCODE
where t1.PLIST is null and t1.ITEMCODE is null
)select PLIST,ITEMCODE,FPRICE as CALPRICE from cte

Result:
PLIST ITEMCODE CALPRICE
SAM 123WER 1.25
TAS FUJ345 1.78
SAM FUJ345 1.45
MAN 123WER 1.35
TAS SON567 4.55
COL 123WER 1.55

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources