从两个表创建视图，关系一对多 [英] Create View from two table with relation one to many

问题描述

请帮帮我



我想创建两张桌子的关系视图



第一张桌子有6列



Id TariffPointsMainId TariffPointsSubId Discreption Notes数量

1 1 2 oooo sss 200

2 1 3 ooooo sss 100

3 1 4 oooooo sss 300





与第二张桌子相关



Id ParentId TariffName价格

1 0钢0

2 1 SteelSony 200

3 1 SteelNokia 300

4 1 SteelBerry 400



我有创建视图



TariffPointsMainName TariffPointsMainName GrossTotal

钢铁SteelSony 40000

Steel SteelNokia 30000

Steel SteelBery 120000



请帮帮我

Please help me

I want create view from two table with relationship

the first table have 6 Column

Id TariffPointsMainId TariffPointsSubId Discreption Notes Quantity
1 1 2 oooo sss 200
2 1 3 ooooo sss 100
3 1 4 oooooo sss 300


related with second table

Id ParentId TariffName Price
1 0 Steel 0
2 1 SteelSony 200
3 1 SteelNokia 300
4 1 SteelBerry 400

and I have Create View

TariffPointsMainName TariffPointsMainName GrossTotal
Steel SteelSony 40000
Steel SteelNokia 30000
Steel SteelBery 120000

Please Help Me

推荐答案

Hi,

-- Check the following Select Statement, If its working fine for all your Data then make as VIEW using this SQL Statement
SELECT T2.TariffName 'TariffPointsMainName', T1.TariffName 'TariffPointsChildName',
ISNULL((SELECT NotesQuantity FROM Table1 WHERE TariffPointsSubId=T2.ParentId AND TariffPointsMainId=T2.ID),0) * T2.Price 'GrossTotal'
FROM Table2 T1
INNER JOIN Table2 T2 ON T1.ID = T2.ParentId 
WHERE T2.ParentId <>0
ORDER BY T2.ParentId, T2.ID

问候，

GVPrabu

Regards,
GVPrabu

这篇关于从两个表创建视图，关系一对多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！