One or the other...

A

Aleks

This may sound a little weird ... I have a table with data, some records
have field "OrderNo" NULL, and others have it as value = 1

If the value is null i need to run one sql and return the result, if its "1"
then it should run a different sql and return another result, so that at the
end I get all results, those coming from records where OrderNo is NULL and
others where OrderNo = 1

Is this possible ? .. below are the two sql's one for each case.

---------------------------------------------

If OrderNo IS NULL

SELECT Activities.ActivityId,
Activities.CaseId,Cases.Id,Cases.CaseId,Activities.FirmId,
Activities.ActType, Activities.ActDesc, Activities.DateInitiated,
Activities.LastModified,Cases.CaseId, Cases.AlienId, Users.UserId,
Users.Mailstr, Users.MaidenNm
FROM ((dbo.Activities LEFT JOIN dbo.Cases ON dbo.Activities.CaseId =
dbo.Cases.Id) ) INNER JOIN dbo.Atts ON dbo.Atts.CaseId = dbo.Cases.Id
INNER JOIN Users ON Activities.responsible = Users.UserId
WHERE Activities.FirmId = 1 AND (Activities.ActType = 'TASK' OR
Activities.ActType = 'DEADLINE' ) AND complete ='0' AND Atts.UserId = 3
and cases.archived <> 1


------------------------------------------------

If OrderNo = 1

SELECT Activities.ActivityId, Activities.CaseId,Activities.FirmId,
Activities.ActType, Activities.ActDesc, Activities.DateInitiated,
Activities.LastModified,Users.UserId, Users.Mailstr, Users.MaidenNm
FROM Activities INNER JOIN Users ON Activities.responsible = Users.UserId
where caseid = 3 and Activities.FirmId = 1 AND (Activities.ActType =
'TASK' OR Activities.ActType = 'DEADLINE' ) AND complete ='0'

Or is there a way to combine both in one statement ?

Aleks
 
S

Sylvain Lafontaine

You can combine both with an UNION if the list of fields are of the same
type on both SELECT. Use Null or empty strings to make sure that both
Select have the same number/type of arguments.

S. L.
 
S

Sylvain Lafontaine

Here it is:

SELECT Activities.ActivityId, Activities.CaseId, Activities.FirmId,
Activities.ActType, Activities.ActDesc, Activities.DateInitiated,
Activities.LastModified,

Users.UserId, Users.Mailstr, Users.MaidenNm,

Cases.Id, Cases.CaseId, Cases.AlienId

FROM ((dbo.Activities LEFT JOIN dbo.Cases ON dbo.Activities.CaseId =
dbo.Cases.Id) ) INNER JOIN dbo.Atts ON dbo.Atts.CaseId = dbo.Cases.Id
INNER JOIN Users ON Activities.responsible = Users.UserId
WHERE Activities.FirmId = 1 AND (Activities.ActType = 'TASK' OR
Activities.ActType = 'DEADLINE' ) AND complete ='0' AND Atts.UserId = 3
and cases.archived <> 1
AND OrderNo IS NULL

UNION ALL /* In your case, the ALL option might be optional. */
SELECT Activities.ActivityId, Activities.CaseId, Activities.FirmId,
Activities.ActType, Activities.ActDesc, Activities.DateInitiated,
Activities.LastModified,

Users.UserId, Users.Mailstr, Users.MaidenNm,

0, 0, 0 /* Notice these three 0, necessary to match the number of fields
in both Select statement. */

FROM Activities INNER JOIN Users ON Activities.responsible = Users.UserId
where caseid = 3 and Activities.FirmId = 1 AND (Activities.ActType =
'TASK' OR Activities.ActType = 'DEADLINE' ) AND complete ='0'
AND OrderNo = 0


Nota: if you don't put the option ALL after the UNION statement, SQL-Server
will adds in implicit Group By and delete any duplicate. This may or may
not have effect on your result in your case. See the BOL for more details.

You should also use alias for the names of your tables.

S. L.
 
Top