I have the following tables:
tblAccount:
-Account
tblAmount:
-ProjectID
-Account
-Amount1
-Amount2
tblOrder:
-OrderID
-ProjectID
-Account
-Amount
tblTransaction:
-TransactionID
-ProjectID
-Account
-Amount
I would like to show all accounts in tblAccount and if there are amount
values on the accounts in the other tables they should be shown next to
the account number. If there are no values in the other tables the
account without value should still be shown.
Which is the best way to do this, a view or sp and with which syntax?
Regards,
SThis is a multi-part message in MIME format.
--=_NextPart_000_1050_01C6EBFD.FFAA9360
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Something like:
SELECT
ProjectID,
Account,
OrderAmt =3D isnull(( SELECT sum( Amount ) FROM tblOrder WHERE =Account =3D a.Account GROUP BY Account ), 0 )
TransAmt =3D isnull(( SELECT sum( Amount ) FROM tblTransaction WHERE =Account =3D a.Account GROUP BY Account ), 0 )
FROM tblAmount a
VIEW or Stored Procedure sorta depends upon how you will use this, and =how often you will use it.
On Another Note: [tbl] as a table prefix is 'old school'. Actually 3 =wasted keystrokes since they provide no additional value. (Make every =keystroke useful.) You know it is a table because it follows the FROM =keyword.
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
<staeri@.gmail.com> wrote in message =news:1160455107.932890.296640@.m73g2000cwd.googlegroups.com...
>I have the following tables:
> > tblAccount:
> -Account
> > tblAmount:
> -ProjectID
> -Account
> -Amount1
> -Amount2
> > tblOrder:
> -OrderID
> -ProjectID
> -Account
> -Amount
> > tblTransaction:
> -TransactionID
> -ProjectID
> -Account
> -Amount
> > I would like to show all accounts in tblAccount and if there are =amount
> values on the accounts in the other tables they should be shown next =to
> the account number. If there are no values in the other tables the
> account without value should still be shown.
> > Which is the best way to do this, a view or sp and with which syntax?
> > Regards,
> > S
>
--=_NextPart_000_1050_01C6EBFD.FFAA9360
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Something like:
SELECT
=ProjectID,
=Account,
OrderAmt =3D =isnull(( SELECT sum( Amount ) FROM tblOrder WHERE Account =3D a.Account GROUP BY =Account ), 0 )
TransAmt =3D =isnull(( SELECT sum( Amount ) FROM tblTransaction WHERE Account =3D a.Account GROUP BY =Account ), 0 )
FROM tblAmount a
VIEW or Stored Procedure sorta depends =upon how you will use this, and how often you will use it.
On Another Note: [tbl] as a table =prefix is 'old school'. Actually 3 wasted keystrokes since they provide no additional =value. (Make every keystroke useful.) You know it is a table because it follows =the FROM keyword.
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
wrote in message news:1160455107.932890.296640@.m73g2000cwd.googlegroups.com...>I =have the following tables:> > tblAccount:> -Account> => tblAmount:> -ProjectID> -Account> -Amount1> -Amount2> > tblOrder:> -OrderID> =-ProjectID> -Account> -Amount> > tblTransaction:> -TransactionID> -ProjectID> -Account> =-Amount> > I would like to show all accounts in tblAccount and if there =are amount> values on the accounts in the other tables they should be =shown next to> the account number. If there are no values in the other =tables the> account without value should still be shown.> => Which is the best way to do this, a view or sp and with which syntax?> => Regards,> > S>
--=_NextPart_000_1050_01C6EBFD.FFAA9360--
, hence the underscores. Oh, well...|||