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.
| Author |
Topic |
|
mholman
Starting Member
8 Posts |
 Posted - 2009-07-20 : 18:12:27
|
| Okay, so I've made my database and now that I'm having to pull everything realize that there is a problem with my table format and hopefully i can rectify this by using an alias or something, but i don't know how.so i have these 2 tables that the issue is with.invoices:inv_id (PK), fund_id, trans_1, trans_2, trans_3,...trans_10,paid,...transactions:trans_id (PK), amount, ttype_idand you may already see the problem,trans_1, trans_2 are all different trans_id keys in my other table transactions, (referencing the primary key).this seemed like a decent idea until i want to call all the transactions at once in a join which it is not wanting to let me do because i am joining the transactions table twice.how can i fix that, and have unique column names for each transactions columns (e.g. amount_1, type_1, amount_2...)? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-07-20 : 18:38:57
|
[code]select *from invoices i inner join transactions t1 on i.trans_1 = t1.trans_id inner join transactions t2 on i.trans_2 = t2.trans_id[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2009-07-20 : 19:31:25
|
| I hope this doesn't sound too snarky but....Why are you putting an array (Tran1 thru Tran10) into your invoice table? An Invoice is an invoice and a transaction is a transaction. Wouldn't it make more sense to leave the transaction information in the transaction table and then create a Parent-Child relationship between the two using the invoice key? Then your join becomes simple.=======================================Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-20 : 19:41:12
|
quote:
Originally posted by khtan
select *from invoices i inner join transactions t1 on i.trans_1 = t1.trans_id inner join transactions t2 on i.trans_2 = t2.trans_id
LEFT JOIN in case all columns are not populated.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
mholman
Starting Member
8 Posts |
Posted - 2009-07-21 : 09:41:40
|
| i have done this and it works, but the reason i did it this way was because i don't know how to make a parent child relation like you mentioned (and needed to have something) but if someone would be able to explain to me how to do that i would be delighted and probably implement this, because having more than 10 possible transactions per invoice, while not needed for my company, may be needed for another company that i have just been told will be using my program.Also, I realize using the name transaction may be misleading. An invoice is essentially a list of transactions. On one invoice you would have all of the things a client would be billed for in a month and each of them is a "transaction". maybe a better word would be charge items, but i don't really know.nevermind, i don't know what i was thinking with that, i just talked to my friend and i realize that i should put inv_id in the transactions table (which i think is what the second reply was about). i really don't know why i didn't do it that way, i did it like that for literally every other table |
|
|
|
|
|
|
|
|
need help with joins