Create view from two table - SQL 2008

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

Create view from two table - SQL 2008

Author

Topic

Vaishu
Posting Yak Master

178 Posts

Posted - 2011-02-16 : 03:26:39

HI
I am trying to create a view in sql 2008 using the below two table. I need to take ALL rows from "TblconPRc" and rows from "TblDEFPRc" which are not in "TblconPRc". Identical columns on both table is PLIST and ITEMCODE. I appriciate any help.

TblconPRc
CUSTID---PLIST----ITEMCODE----FPRICE
SAMTR----SAM-------123WER------1.25
GANTS----TAS-------FUJ345------1.78
SAMTR----SAM-------FUJ345------1.45
MANTA----MAN-------123WER------1.35

TblDEFPRc
PLIST----ITEMCODE----UNITPRC
TAS-------SON567------4.55
COL-------123WER------1.55
SAM-------123WER------1.55
TAS-------FUJ345------1.98
SAM-------FUJ345------1.65
MAN-------123WER------1.75

EXPECTED RESULT IS :
CUSTID---PLIST----ITEMCODE----FPRICE
SAMTR----SAM-------123WER------1.25
GANTS----TAS-------FUJ345------1.78
SAMTR----SAM-------FUJ345------1.45
MANTA----MAN-------123WER------1.35
---------TAS-------SON567------4.55
---------COL-------123WER------1.55

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-16 : 03:35:49

check if the below is what you are looking for

SElect CUSTID,PLIST,ITEMCODE,FPRICE From TblconPRc

Union All

SElect CUSTID,B.PLIST,B.ITEMCODE,B.FPRICE
From TblconPRc A
Right Join TblDEFPRc B on (A.PLIST=B.PLIST and A.ITEMCODE=B.ITEMCODE)
Where A.CustID is null

Cheers
MIK

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-02-16 : 04:00:59

Hi, check it once


create table #TblconPRc 
(
CUSTID varchar(20),
PLIST varchar(20),
ITEMCODE varchar(20),
FPRICE float
)
go
insert #TblconPRc
select 'SAMTR','SAM','123WER',1.25 union all
select 'GANTS','TAS','FUJ345',1.78 union all
select 'SAMTR','SAM','FUJ345',1.45 union all
select 'MANTA','MAN','123WER',1.35
go
create table #TblDEFPRc
(
PLIST varchar(20),
ITEMCODE varchar(20),
UNITPRC float
)
go
insert #TblDEFPRc
select 'TAS','SON567',4.55 union all
select 'COL','123WER',1.55 union all
select 'SAM','123WER',1.55 union all
select 'TAS','FUJ345',1.98 union all
select 'SAM','FUJ345',1.65 union all
select 'MAN','123WER',1.75

go
;with cte as
(
select *
from #TblconPRc 

union all

select null,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 * from cte

--Ranjit

Vaishu
Posting Yak Master

178 Posts

Posted - 2011-02-16 : 05:15:44

HI
Thanks for the Quick response. The question was slightly wrong from my side .The solution works but giving diffrent result:
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

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

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-02-16 : 05:26:54

;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

--Ranjit

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-16 : 05:28:55

@OP incorrect results were due to my query?

Vaishu
Posting Yak Master

178 Posts

Posted - 2011-02-16 : 06:58:58

HI
RANJIT and WIK . Thanks a lot guys. Both of your query works. I have confused with one of the row in the table when I first try with your solutions. So now its works

Many many thanks for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources