More than one query to return results

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 2005 Forums

Transact-SQL (2005)

More than one query to return results

Author

Topic

CommonSide
Starting Member

3 Posts

Posted - 2008-02-12 : 11:40:03

Hi, Im an amature SQL user. I have an access database that I have two querys in. A third query, querys the first querys and returns the correct set of results. What I want to do is to be able to run A SQL statement in Query analyser that returns the same results (i.e no views are created).

The first query called AccountTimingTotals SQL syntax is:
SELECT dbo_Scheme.Code, dbo_Scheme.Version, dbo_Status.Description AS Status, Sum(dbo_AccountTiming.Amount) AS SumOfAmount, dbo_AccountCode.Code AS AccountCode, dbo_AccountCode.Description AS AcountDesc
FROM dbo_AccountCode RIGHT JOIN ((dbo_Scheme INNER JOIN dbo_AccountTiming ON dbo_Scheme.SchemeID = dbo_AccountTiming.SchemeID) INNER JOIN dbo_Status ON dbo_Scheme.Status = dbo_Status.StatusID) ON dbo_AccountCode.AccountCodeID = dbo_AccountTiming.Account
GROUP BY dbo_Scheme.Code, dbo_Scheme.Version, dbo_Status.Description, dbo_AccountCode.Code, dbo_AccountCode.Description;

The second query called SchemeTimingTotals SQL syntax is:
SELECT dbo_Scheme.Code, dbo_Scheme.Version, dbo_Status.Description AS Status, dbo_AccountCode.Description AS AccountDesc, dbo_AccountCode.Code AS AccountCode, Sum([AdoptionAmount]+[TotalVar]) AS SchemeAccount
FROM ((dbo_Scheme LEFT JOIN dbo_SchemeAccount ON dbo_Scheme.SchemeID = dbo_SchemeAccount.SchemeID) INNER JOIN dbo_Status ON dbo_Scheme.Status = dbo_Status.StatusID) LEFT JOIN dbo_AccountCode ON dbo_SchemeAccount.Account = dbo_AccountCode.AccountCodeID
GROUP BY dbo_Scheme.Code, dbo_Scheme.Version, dbo_Status.Description, dbo_AccountCode.Description, dbo_AccountCode.Code;

And the Third query that querys the first two statement is:

SELECT SchemeAccountTotals.Code, SchemeAccountTotals.Version, SchemeAccountTotals.Status, SchemeAccountTotals.AccountCode, SchemeAccountTotals.AccountDesc, (([SchemeAccount])) AS SA, (([SumOfAmount])) AS Amount
FROM SchemeAccountTotals INNER JOIN AccountTimingTotals ON (SchemeAccountTotals.Code = AccountTimingTotals.Code) AND (SchemeAccountTotals.Version = AccountTimingTotals.Version) AND (SchemeAccountTotals.Status = AccountTimingTotals.Status) AND (SchemeAccountTotals.AccountCode = AccountTimingTotals.AccountCode) AND (SchemeAccountTotals.AccountDesc = AccountTimingTotals.AcountDesc);

Any ideas on how I can do this Im a bit stuck.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-12 : 12:21:47

One possibility , is to include the first 2 sql statements as Views , and then use the 3rd statement , referencing the the 2 views

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-12 : 12:31:28

or use them as subqueries

SELECT SchemeAccountTotals.Code, SchemeAccountTotals.Version, SchemeAccountTotals.Status, SchemeAccountTotals.AccountCode, SchemeAccountTotals.AccountDesc, (([SchemeAccount])) AS SA, (([SumOfAmount])) AS Amount
FROM (firstqueryhere)SchemeAccountTotals INNER JOIN (secondquery here)AccountTimingTotals ON (SchemeAccountTotals.Code = AccountTimingTotals.Code) AND (SchemeAccountTotals.Version = AccountTimingTotals.Version) AND (SchemeAccountTotals.Status = AccountTimingTotals.Status) AND (SchemeAccountTotals.AccountCode = AccountTimingTotals.AccountCode) AND (SchemeAccountTotals.AccountDesc = AccountTimingTotals.AcountDesc);

CommonSide
Starting Member

3 Posts

Posted - 2008-02-14 : 05:26:58

Thanks all! I used the subquery version at the end and it works great! Many Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources