help with join - 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

SQL Server Development (2000)

help with join

Author

Topic

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2008-01-23 : 08:40:59

I have the following:

Select AccountNumber,FundID,FundTransactionType, PricePerShare, ShareAmount, GrossAmount, TradeDate, PostingDate
From FundTransactionLines
Where AccountNumber in ('10058','10061','10184')

I want to add SSN and MailingAddressId which is in the AccountInfo table where AccountNumber = AccountNumber

but then I want to add NameLines and AddressLines which is in the MailingAddresses table but would need MailingAddressId = MailingAddressId from AccountInfo table

any help greatly appreciated

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-01-23 : 08:56:52

I created following demo in test database. Hope this is what you want

create table FundTransactionLines
(
AccountNumber int primary key ,
FundID int,
FundTransactionType varchar(30)
)

insert into FundTransactionLines values (1,1,'some')
insert into FundTransactionLines values (2,2,'some two')

create table AccountInfo
(
AccountNumber int references FundTransactionLines(AccountNumber),
SSN int ,
MailingAddressId int primary key,

)

insert into AccountInfo values (1,1111,1)
insert into AccountInfo values (2,2222,2)

create table MailingAddresses
(
MailingAddressId int primary key references AccountInfo(MailingAddressId) ,

NameLines varchar(30) ,
AddressLines varchar(30),

)

insert into MailingAddresses values (1,'GK','add 1')
insert into MailingAddresses values (2,'GK 2','address 11')

select f.*,a.*,m.AddressLines from
FundTransactionLines f inner join
AccountInfo a on f.AccountNumber=a.AccountNumber inner join
MailingAddresses m on a.MailingAddressId= m.MailingAddressId

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-23 : 08:57:28

Great!
Now all we here need is your table layouts because we don't have access to your Enterprise Manager.

E 12°55'05.25"
N 56°04'39.16"

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2008-01-23 : 08:59:19

I don't actually want to create a table though

that data is all there im just trying to create a join to view it

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-01-23 : 09:02:16

I created tables for demo purpose and to simulate your problem. Hope you get idea about joins to make.

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2008-01-23 : 09:08:07

i thought it would be something along the lines of this but not sure..

Select a.AccountNumber,a.FundID,a.FundTransactionType, a.PricePerShare, a.ShareAmount, a.GrossAmount, a.TradeDate, a.PostingDate, b.SSN, b.MailingAddressId, c.NameLine, c.AddressLines
From FundTransactionLines a, AccountInfo b, MailingAddress C
bla bla (help here)
Where AccountNumber in ('10058','10061','10184')

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-01-23 : 09:18:51

Its up to you give it a try. As Peso mentioned, you havn't provided any schema for your tables and want others to visulaize your scenario. Query I have given i based on my visualtization of your scenario. This best I could think of. Hope it helps.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-01-23 : 09:22:28

have you looked in Books Online yet? it's your best friend, trust me.

Sunil has given you the basics though...


select SomeStuff
from Table1
  join Table2 on table1.ColToJoinOn = table2.ColToJoinOn
--then just keep adding tables the same way...
  join Table3 on table1.OtherJoinCol = table3.OtherJoinCol

as Peso has said already, unless you give us your table details etc, we can only give you general / vague answers

Em

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2008-01-23 : 09:26:53

this seems to work..

Select a.AccountNumber,a.FundID, d.FundName, a.FundTransactionType, e.TransactionTypeName, b.SSNorTIN, b.MailingAddressId, c.NameLines, c.AddressLines, a.PricePerShare, a.ShareAmount, a.GrossAmount, a.TradeDate, a.PostingDate
From FundTransactionLines a, AccountInfo b, Mailingaddresses c, FundInfo d, FundTransactionTypes e
Where a.AccountNumber = b.AccountNumber
And b.MailingAddressId = c.MailingAddressId
And a.FundID = d.FundId
And a.FundTransactionType = e.TransactionTypeCode
And a.AccountNumber in ('10058','10061','10184')

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-01-23 : 09:31:00

non-ansi joins! yuk yuk yuk

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-01-23 : 09:32:59

[code]
Select a.AccountNumber,a.FundID, d.FundName, a.FundTransactionType, e.TransactionTypeName, b.SSNorTIN, b.MailingAddressId, c.NameLines, c.AddressLines, a.PricePerShare, a.ShareAmount, a.GrossAmount, a.TradeDate, a.PostingDate
From FundTransactionLines a
join AccountInfo b on a.AccountNumber = b.AccountNumber
join Mailingaddresses c on b.MailingAddressId = c.MailingAddressId
join FundInfo d on a.FundID = d.FundId
join FundTransactionTypes e on a.FundTransactionType = e.TransactionTypeCode

Where a.AccountNumber in ('10058','10061','10184')
[/code]

Em

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2008-01-23 : 10:12:19

quote:

Originally posted by elancaster


Select a.AccountNumber,a.FundID, d.FundName, a.FundTransactionType, e.TransactionTypeName, b.SSNorTIN, b.MailingAddressId, c.NameLines, c.AddressLines, a.PricePerShare, a.ShareAmount, a.GrossAmount, a.TradeDate, a.PostingDate 
From FundTransactionLines a
	join AccountInfo b on  a.AccountNumber = b.AccountNumber
	join Mailingaddresses c on  b.MailingAddressId = c.MailingAddressId
	join FundInfo d on  a.FundID = d.FundId
	join FundTransactionTypes e on a.FundTransactionType = e.TransactionTypeCode

Where  a.AccountNumber in ('10058','10061','10184')

thank you

it was sloppy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources