Multi Table query - 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)

Multi Table query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-23 : 08:56:21

mart writes "OK this is a bit of a monster, anyway here goes.

I have six tables from which I wish to extract certain bits of data, the tables are as follows :-

Bill_Transaction alias BT
Bill_Code
Bill_date
OS_gross_balance
Acc_Cde
Job_Code

Bill_Input_doc alias BID
Bill_Code
Crncy_Cde
Job_code

Company alias COMP
Co_cde
Co_name

Grp alias GRP
Grp_cde
Ou_cde

job alias JB
job_code
grp_cde

Currency alias CURR
Crncy_cde
Curr_Name

OK I wish to extract BT.BILL_CODE, BILL_DATE,OS_GROSS_BALANCE,ACC_CDE, BID.CRNCY_CDE, CURR.Curr_NAME, GRP.OU_CDE ,COMP.co_name where the OS_GROSS_BAL field is > 0 using the BILL_TRANSACTION using table as the main driver, also allowing for the fact that in some cases data may be incomplete ie a bill_code may be missing from Bill_Input_doc and so you may not be able to locate the correct currency name.

Currently I am using a statement similar to the following :-
select bt.bill_cde,
bt.os_gross_bal,
bt.acc_cde,
bt.bill_dt,
bid.crncy_cde,
comp.nme,
curr.nme
from is00dba.bill_transaction bt
left outer join is00dba.bill_input_doc bid on bt.bill_cde = bid.bill_cde
left outer join is00dba.company comp on bt.acc_cde = comp.co_cde
left outer join is00dba.currency curr on bid.crncy_cde = curr.crncy_cde
where
bt.os_gross_bal > 0
order by bt.acc_cde
However I would like to streamline this and also incorporate OU_CDE into the result set

Any help would be appreciated - martin"

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-07-23 : 14:55:41

Please post the table relationships. Especially GRP to the others.

Sarah Berger MCSD

martins
Starting Member

1 Post

Posted - 2002-07-24 : 09:48:23

The Table relationships are as follows :-
Bill_transaction
Bill_cde ------> Bill_input.doc.Bill_code
Bill_date Bill_input.doc.Crncy_Cde ------> Currency.Crncy_code
os_gross_bal Currency.Name
Acc_cde -------> Company.co_cde
Company.Name

job_cde -------> job.job_cde
job.grp_cde -------> grp.grp_cde
grp.ou_cde
Remember I wish to extract the following :-
Bill_Transaction.Bill_cde
Bill_Transaction.acc_cde
Bill_Transaction.job_cde
Bill_Transaction.os_gross_bal
Bill_Transaction.Bill_Date
Bill_input_doc.Crncy_cde
Currency.Currency_name
Grp.ou_cde
Company.name

Thanks for your help

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-24 : 10:00:33

I'm not sure what SQL-dialect you are using, but as this is a SQL Server forum, you will probably get better response by posting actual transact-sql code rather than the notation you have provided....

<O>

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-07-24 : 11:02:15

select bt.bill_cde,
bt.os_gross_bal,
bt.acc_cde,
bt.bill_dt,
bid.crncy_cde,
comp.nme,grp.ou_cde,
curr.nme
from is00dba.bill_transaction bt
left outer join is00dba.bill_input_doc bid on bt.bill_cde = bid.bill_cde
left outer join is00dba.company comp on bt.acc_cde = comp.co_cde
left outer join is00dba.currency curr on bid.crncy_cde = curr.crncy_cde
<JOIN Type> Job JB ON JB.Job_Code = BT.Job_Code <JOIN Type> Grp ON JB.grp_cde = grp.grp_cde
where
bt.os_gross_bal > 0
order by bt.acc_cde

you might have to change the order of the joins around to get the data you want. Or you can also use a derived table to get the grp.ou_cde field. Post if you want to use a derived table. Generally, the best way to streamline queries using many tables is to create views of several tables, and use only 2 or 3 views rather than many tables.

Sarah Berger MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources