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)
 combining queries

Author  Topic 

PeterG
Posting Yak Master

156 Posts

Posted - 2003-08-06 : 15:35:00
Is it possible to combine these 2 queries into one? Pls help.

SELECT DISTINCT A.txtName
FROM ApprovalGroup As AG INNER JOIN UserGroups AS UG ON
AG.txtInternetNum = UG.txtInternetNum AND AG.intViewGroup = UG.lngGroupNum
INNER JOIN Account AS A ON UG.txtAccountNum = A.txtAccountNum AND UG.txtInternetNum = A.txtInternetCompNum
WHERE (AG.txtUserID = 'tester1') AND (AG.txtInternetNum = '9570')
ORDER BY A.txtName

select DISTINCT a.txtName, t.blnsent
from transactions t inner join account a on t.txtAccountNum = a.txtaccountnum
where a.txtinternetcompnum = '9570' and
t.dtecycledate = (Select MAX(t1.dtecycledate) from transactions t1 where t1.txtinternetnum = '9570')
and t.blnsent = 0
ORDER By a.txtName

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-06 : 15:54:43
Depends what you want




SELECT DISTINCT 'QUERY1' AS SOURCE, A.txtName, Null AS Col3
FROM ApprovalGroup As AG
INNER JOIN UserGroups AS UG
ON AG.txtInternetNum = UG.txtInternetNum
AND AG.intViewGroup = UG.lngGroupNum
INNER JOIN Account AS A ON UG.txtAccountNum = A.txtAccountNum
AND UG.txtInternetNum = A.txtInternetCompNum
WHERE (AG.txtUserID = 'tester1')
AND (AG.txtInternetNum = '9570')
UNION ALL
SELECT DISTINCT 'QUERY2', a.txtName, t.blnsent
FROM transactions t
INNER JOIN account a
ON t.txtAccountNum = a.txtaccountnum
WHERE a.txtinternetcompnum = '9570'
AND t.dtecycledate = ( SELECT MAX(t1.dtecycledate)
FROM transactions t1
WHERE t1.txtinternetnum = '9570')
AND t.blnsent = 0
ORDER BY a.txtName

-- OR 1 Row per
SELECT * FROM (
SELECT DISTINCT 'QUERY1' AS SOURCE, A.txtName, Null AS Col3
FROM ApprovalGroup As AG
INNER JOIN UserGroups AS UG
ON AG.txtInternetNum = UG.txtInternetNum
AND AG.intViewGroup = UG.lngGroupNum
INNER JOIN Account AS A ON UG.txtAccountNum = A.txtAccountNum
AND UG.txtInternetNum = A.txtInternetCompNum
WHERE (AG.txtUserID = 'tester1')
AND (AG.txtInternetNum = '9570')
) AS A
INNER JOIN
(
SELECT DISTINCT 'QUERY2', a.txtName, t.blnsent
FROM transactions t
INNER JOIN account a
ON t.txtAccountNum = a.txtaccountnum
WHERE a.txtinternetcompnum = '9570'
AND t.dtecycledate = ( SELECT MAX(t1.dtecycledate)
FROM transactions t1
WHERE t1.txtinternetnum = '9570')
AND t.blnsent = 0
) AS B
ON A.txtName = B.txtNAme
ORDER BY a.txtName

-- Take your pick




Brett

8-)

SELECT POST=NewId()
Go to Top of Page
   

- Advertisement -