Query Part 2 - 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 2000 Forums

Transact-SQL (2000)

Query Part 2

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2003-01-19 : 01:53:29

Hi there

I had 3 tables which the structure like this:

tblAsset:
+ AssetID int
+ Name

tblSplitSet:
+ SplitSetID
+ AssetID

tblSplit:
+ SplitID
+ SplitSetID
+ Percentage
+ Code

The sample data for each table are:
tblAsset:
+ 1; Mobile Phone
+ 2; PC
+ 3; Laptop

tblSplitSet
+ 10; 1
+ 11; 2
+ 12; 3

tblSplit:
+ 100; 10; 50; AAAA
+ 101; 10; 50; BBBB
+ 102; 11; 25; AAAA
+ 103; 11; 25; CCCC
+ 104; 11; 50; DDDD
+ 105; 12; 100; DDDD

I create a view joining these 3 tables:
+ 1; Mobile Phone; 100; 10; 50; AAAA
+ 1; Mobile Phone; 101; 10; 50; BBBB
+ 2; PC; 102; 11; 25; AAAA
+ 2; PC; 103; 11; 25; CCCC
+ 2; PC; 104; 11; 50; DDDD
+ 3; Laptop; 105; 12; 100; DDDD

If I use that view and and filter using WHERE SplitName = 'DDDD'
normally will return:
+ 2; PC; 104; 11; 50; DDDD
+ 3; Laptop; 105; 12; 100; DDDD

But I still want display the asset and split set associate with the SplitID = 'DDDD' which are these:
+ 2; PC; 102; 11; 25; AAAA
+ 2; PC; 103; 11; 25; CCCC
+ 2; PC; 104; 11; 50; DDDD
+ 3; Laptop; 105; 12; 100; DDDD

Is this possible?

Yup the answer is yes ... thanks to NR (below).

Another question comes up: how do add extra field (No Of Split) to tell that "Join Asset and Splitset" has more than 1 split?

So it will be like (extra field at the end!)

+ 2; PC; 102; 11; 25; AAAA; 3
+ 2; PC; 103; 11; 25; CCCC; 3
+ 2; PC; 104; 11; 50; DDDD; 3
+ 3; Laptop; 105; 12; 100; DDDD; 1

Thank you.

Edited by - valdyv on 01/19/2003 16:11:00

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-19 : 05:43:02

select * from view where tblSplit_Code = 'DDDD'
union
select * from view where tblSplit_SplitSetID in (select tblSplit_SplitSetID from view where tblSplit_Code = 'DDDD')

select * from view
where tblSplit_Code = 'DDDD'
or
tblSplit_SplitSetID in (select tblSplit_SplitSetID from view where tblSplit_Code = 'DDDD')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources