Command with UNION ALL (how to set condition)

Question

0.00/5 (No votes)

See more:

Dear All,
Below is my SQL query in which I have used UNION All In my outer query. so I want to select data onwards particular date . Is it possible? if yes please reply.

[CODE]--------------------------------------------------------------------------------------------------
--KNOW-HOWS
-------------------
	SELECT KnowHowID AS Id,
			FirstName + ' '+ LastName AS PostedBY,--PostedByID
			Title AS Title,
			ImagePath AS PhotoGraph,
			PostedDateTime AS DateTime,
			'knowHows' AS FeatureName,
			'' AS cnt
	FROM uvw_KnowHows 
	WHERE Active=1 AND PostedByID IN( --This query is for all friend of this user
						SELECT FriendID 
						FROM tblFriends 
						WHERE UserID=422973513 AND ISApproved = 1
					UNION ALL
						SELECT UserID
						FROM tblFriends 
						WHERE FriendID =422973513 AND ISApproved = 1
					UNION ALL
						 --This query is for All members in common network with this user
						SELECT FK_UserID 
						FROM tblNetworkMembers 
						WHERE FK_NetworkId IN (
												SELECT FK_NetworkId 
												FROM tblNetworkMembers 
												WHERE FK_UserID=422973513 
											   )
						 )
UNION All
---------------------------------------------------------------------------------------------------------------------
--NEWS-VIEWS
-------------------
	SELECT NewsViewsID AS Id,
			FirstName + ' '+ LastName AS PostedBy,
			Title AS Title,
			ImagePath AS PhotoGraph,
			PostedDateTime AS DateTime, 
			'NewsViews' AS FeatureName,
			'' AS cnt 
	FROM uvw_NewsViews 
	WHERE Active=1 AND PostedByID IN(
						  --This query is for all friend of this user
						SELECT FriendID 
						FROM tblFriends 
						WHERE UserID=422973513 AND ISApproved = 1
					UNION ALL
						SELECT UserID
						FROM tblFriends 
						WHERE FriendID =422973513 AND ISApproved = 1
					UNION ALL
						 --This query is for All members in common network with this user
						SELECT FK_UserID 
						FROM tblNetworkMembers 
						WHERE FK_NetworkId IN (
												SELECT FK_NetworkId 
												FROM tblNetworkMembers 
												WHERE FK_UserID=422973513 
											   )
						) 
UNION All
---------------------------------------------------------------------------------------------------------------------
--PUBLIC IMAGE
-------------------
	SELECT PhotographID AS Id,
			FirstName + ' '+ LastName AS PostedBy,
			Title AS Title,
			[Path] AS PhotoGraph,
			PostedDateTime AS DateTime,
			'PublicImage' AS FeatureName ,
			'' AS cnt
	FROM uvw_PublicAlbum  
	WHERE Active=1 AND UserID IN (
						 --This query is for all friend of this user
						SELECT FriendID 
						FROM tblFriends 
						WHERE UserID=422973513 AND ISApproved = 1
					UNION ALL
						SELECT UserID
						FROM tblFriends 
						WHERE FriendID =422973513 AND ISApproved = 1
					UNION ALL
						 --This query is for All members in common network with this user
						SELECT FK_UserID 
						FROM tblNetworkMembers 
						WHERE FK_NetworkId IN (
												SELECT FK_NetworkId 
												FROM tblNetworkMembers 
												WHERE FK_UserID=422973513 
											   )
					 )

Posted 25-Mar-11 3:32am

Maddy_1008

Add a Solution

Comments

Sandeep Mewara 25-Mar-11 10:12am

Not clear.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Stryder_1 · Accepted Answer · 2011-03-25T04:36:00

Sure, you can wrap your whole query above in another select statement and then filter on the date:

Select Id, PostedBy, Title, PhotoGraph, DateTime, FeatureName, cnt 
From 
( 
[your query here]
) 

where DateTime > '1/1/2011'

This would be easier to write, however, it may be more efficient if you limited each sub select with the date range. This would depend on how your server optimizes the query.