Joinning three script to get one result set

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

Transact-SQL (2000)

Joinning three script to get one result set

Author

Topic

sqldbaa
Starting Member

32 Posts

Posted - 2007-12-19 : 09:54:07

I have written threee script
I need to combine the results

Script 1:accounts that were given quotes between jan 1 and dec 1 but have no order

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '01/01/07'
SET @EndDate = '12/01/07'

SELECT DISTINCT
T.ID, T.AccountID, Q.LastDate
FROM
Table1 T
INNER JOIN table2 E ON E.TID = T.TID
INNER JOIN Table3 X ON X.TID = T.TID
INNER JOIN
(
SELECT TID, MAX(Date) AS LastDate FROM table4 WHERE Type = 'ADD' AND Date BETWEEN @StartDate AND @EndDate
GROUP BY TID
) Q ON Q.TID = T.TID
WHERE
E.order < 0
ORDER BY T.TID

Script 2: accounts with order but no quotes

SELECT DISTINCT
T.TID, T.AccountID
FROM
Table1 T
INNER JOIN table2 E ON E.TID = T.TrID
LEFT JOIN table3 Q ON Q.TID = T.TID
WHERE
Q.FID IS NULL AND
E.order >= 0

Script 3:-- accounts that were given quotes prior to dec 1 but haven't processed yet

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '01/01/07'
SET @EndDate = '12/01/07'

SELECT DISTINCT
T.ID, T.AccountID, Q.LastDate
FROM
Table1 T
INNER JOIN Table3 X ON X.TID = T.TID
INNER JOIN
(
SELECT TID, MAX(Date) AS LastDate FROM Table4 WHERE Type = 'ADD' AND Date BETWEEN @StartDate AND @EndDate
GROUP BY TID
) Q ON Q.TID = T.TID
LEFT JOIN
(
SELECT DISTINCT AccountID FROM Table5 WHERE Date BETWEEN '01/01/07' AND '12/01/07'
) A ON A.ID = T.AccountID
WHERE
A.AccountID IS NULL

I have to combine these three scripts and retieve one result set

Is it possible

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-19 : 10:03:58

In what way you want to combine them? If you want to merge them, the resultset of all scripts is not same.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-19 : 10:17:37

Try this:-

SELECT DISTINCT
T.ID, T.AccountID, Q.LastDate
FROM
Table1 T
INNER JOIN table2 E ON E.TID = T.TID
INNER JOIN Table3 X ON X.TID = T.TID
INNER JOIN
(
SELECT TID, MAX(Date) AS LastDate FROM table4 WHERE Type = 'ADD' AND Date BETWEEN @StartDate AND @EndDate
GROUP BY TID
) Q ON Q.TID = T.TID
WHERE
E.order < 0
ORDER BY T.TID

UNION ALL

SELECT DISTINCT
T.TID, T.AccountID,NULL
FROM
Table1 T
INNER JOIN table2 E ON E.TID = T.TrID
LEFT JOIN table3 Q ON Q.TID = T.TID
WHERE
Q.FID IS NULL AND
E.order >= 0

UNION ALL

SELECT DISTINCT
T.ID, T.AccountID, Q.LastDate
FROM
Table1 T
INNER JOIN Table3 X ON X.TID = T.TID
INNER JOIN
(
SELECT TID, MAX(Date) AS LastDate FROM Table4 WHERE Type = 'ADD' AND Date BETWEEN @StartDate AND @EndDate
GROUP BY TID
) Q ON Q.TID = T.TID
LEFT JOIN
(
SELECT DISTINCT AccountID FROM Table5 WHERE Date BETWEEN '01/01/07' AND '12/01/07'
) A ON A.ID = T.AccountID
WHERE
A.AccountID IS NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 10:31:35

Script 1

UNION ALL

Script 2

UNION ALL

Script 3

Please notice that there are 3 columns in first and last script and only 2 columns in Script 2.
There you have to add ", NULL" to the select list.

E 12°55'05.25"
N 56°04'39.16"

sqldbaa
Starting Member

32 Posts

Posted - 2007-12-20 : 09:10:15

Thanks for the response.

But as im declaring the Start date and end date on script 1 and script 3 , im getting error.

Any idea, plz help me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-20 : 09:14:32

declare it only once above the three scripts

DECLARE...

Script1

UNION ALL

Script2

UNION ALL

Script3

sqldbaa
Starting Member

32 Posts

Posted - 2007-12-20 : 09:23:42

but im getting error at the end of the first script

Server:Msg156,level 15, State 1, Line22
Incorrect sytax near the keyword 'UNION'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-20 : 09:31:47

Remove Order by clause from first script. You cant directly uion all the ordered results. Do order by at the end of the last script

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-20 : 09:33:52

Take the ORDER BY out of script1. If you want to use ORDER By use ORDER BY as follows

SELECT * FROM

( Script1
UNION ALL
Script2
UNION ALL
Script3
) t
ORDER BY t.TID

sqldbaa
Starting Member

32 Posts

Posted - 2007-12-20 : 10:01:04

Thanks for the immediate response

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 10:08:46

[code]DECLARE @StartDate DATETIME,
@EndDate DATETIME

SELECT @StartDate = '01/01/07',
@EndDate = '12/01/07'

SELECT DISTINCT ID,
AccountID,
LastDate
FROM (
SELECT T.ID,
T.AccountID,
Q.LastDate
FROM Table1 AS T
INNER JOIN Table2 AS E ON E.TID = T.TID
INNER JOIN Table3 AS X ON X.TID = T.TID
INNER JOIN (
SELECT TID,
MAX(Date) AS LastDate
FROM Table4
WHERE Type = 'ADD'
AND Date >= @StartDate
AND Date < @EndDate
GROUP BY TID
) AS Q ON Q.TID = T.TID
WHERE E.Order < 0

UNION ALL

SELECT T.TID,
T.AccountID,
NULL
FROM Table1 AS T
INNER JOIN Table2 AS E ON E.TID = T.TrID
LEFT JOIN Table3 AS Q ON Q.TID = T.TID
WHERE Q.FID IS NULL
AND E.Order >= 0

UNION ALL

SELECT T.ID,
T.AccountID,
Q.LastDate
FROM Table1 AS T
INNER JOIN Table3 AS X ON X.TID = T.TID
INNER JOIN (
SELECT TID,
MAX(Date) AS LastDate
FROM Table4
WHERE Type = 'ADD'
AND Date >= @StartDate
AND Date < @EndDate
GROUP BY TID
) AS Q ON Q.TID = T.TID
LEFT JOIN (
SELECT AccountID
FROM Table5
WHERE Date >= @StartDate
AND Date < @EndDate
) AS A ON A.ID = T.AccountID
WHERE A.AccountID IS NULL
) AS d
ORDER BY TID[/code]

E 12°55'05.25"
N 56°04'39.16"

sqldbaa
Starting Member

32 Posts

Posted - 2007-12-20 : 10:18:10

Is it possible to get the results of three scripts in same table but each thing in different columns

C1-TID,
C2-ACCOUNIT
C3-date
C4-ACCOUNT with E.order < 0
C5-Account E.order >= 0
C6= Account haven't processed

Are the script needs to be changed?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 10:22:13

Yes.
C4-C6, are they COUNT, SUM, MIN, MAX or other aggregation?

E 12°55'05.25"
N 56°04'39.16"

sqldbaa
Starting Member

32 Posts

Posted - 2007-12-20 : 10:41:24

sorry,

C1-TID,
C2-date
C3-ACCOUNTID whose E.order < 0
C4-AccountID whose E.order >= 0
C5= AccountID who haven't processed

C3-C5 ----ID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 10:55:45