Query Join (Union?) - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Query Join (Union?)

Author

Topic

stharish
Starting Member

43 Posts

Posted - 2012-01-09 : 19:13:49

Microsoft SQL Server 2008

Need help with joining the two queries below. Can help me in joining the two queries below?

I'm trying to get a count of pilots where they either 1) were a pilot on a flight OR 2) relocated via ground or airline within the date parameters @from and @to. I have two queries which give me totals for both.

--This query gets distinct pilot user ID's for count 1 above
SELECT DISTINCT FlightCrew.UserID as [UserID]

FROM tm_Flights as Flights

LEFT OUTER JOIN tm_FlightCrew as FlightCrew
ON FlightCrew.FlightID=Flights.FlightID
INNER JOIN tm_Users as Users
ON Users.UserID=FlightCrew.UserID

Where Flights.StatusTypeID <>13
and Flights.ETDLocal BETWEEN @From and @To

--This query gets the distinct pilot user ID's for count 2 above
SELECT Distinct Users.UserID

FROM tr_EntityServices as Services

INNER JOIN tl_ServiceTypes as ServiceTypes
ON ServiceTypes.ServiceTypeID=Services.ServiceTypeID
INNER JOIN tm_Users as Users
ON Users.UserID=Services.EntityKeyID

and Services.StatusTypeID <>13
and ServiceTypes.ServiceTypeID IN ('9','7')
and Services.DateRequired BETWEEN @From and @To

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-09 : 19:30:49

Not quite sure what you mean by joining. Here is how I interpreted your requirement.

--This query gets distinct pilot user ID's for count 1 above
;WITH cte1 AS 
(
SELECT
	DISTINCT FlightCrew.UserID AS [UserID]
FROM
	tm_Flights AS Flights
	LEFT OUTER JOIN tm_FlightCrew AS FlightCrew
		ON  FlightCrew.FlightID = Flights.FlightID
	INNER JOIN tm_Users AS Users
		ON  Users.UserID = FlightCrew.UserID
WHERE
	Flights.StatusTypeID <> 13
	AND Flights.ETDLocal BETWEEN @From AND @To
),
cte2 AS
(
--This query gets the distinct pilot user ID's for count 2 above
SELECT
	DISTINCT Users.UserID
FROM
	tr_EntityServices AS Services
	INNER JOIN tl_ServiceTypes AS ServiceTypes
		ON  ServiceTypes.ServiceTypeID = Services.ServiceTypeID
	INNER JOIN tm_Users AS Users
		ON  Users.UserID = Services.EntityKeyID AND Services.StatusTypeID <> 13 AND 
		    ServiceTypes.ServiceTypeID IN ('9', '7') AND Services.DateRequired 
		    BETWEEN @From AND @To
)
SELECT
	COALESCE(c1.UserId, c2.UserId) AS UserId,
	CASE WHEN c1.UserId IS NOT NULL THEN 'Yes' ELSE 'No' END AS [PilotOnFlight],
	CASE WHEN c2.UserId IS NOT NULL THEN 'Yes' ELSE 'No' END AS [RelocatedViaGround]
FROM
	cte1 c1 FULL JOIN cte2 c2 ON c1.UserId = c2.UserId;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 03:59:55

wont this be sufficient?


SELECT COUNT(*)
FROM
(
your query 1 here
UNION
your query 2
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stharish
Starting Member

43 Posts

Posted - 2012-01-10 : 12:12:00

Hello visakh16. That seems to work well! Just one thing I didn't think of earlier. I want distinct UserID values from each query but I also want distinct values from the union. In other words, if a specific pilot(UserID) flys(query1) and also relocates(query2) I only want to count him once. I'm just looking for a count of pilots who actually did something (flew or relocated) on a particular day.

Would it be a SELECT DISTINCT COUNT or something like that?? This is my first experience with UNIONS.

Current Query:

SELECT COUNT (*)

FROM
(
SELECT DISTINCT FlightCrew.UserID as [UserID]

FROM tm_Flights as Flights

LEFT OUTER JOIN tm_FlightCrew as FlightCrew
ON FlightCrew.FlightID=Flights.FlightID
INNER JOIN tm_Users as Users
ON Users.UserID=FlightCrew.UserID

Where Flights.StatusTypeID <>13
and Flights.ETDLocal BETWEEN @From and @To

UNION

SELECT Distinct Users.UserID

FROM tr_EntityServices as Services

INNER JOIN tl_ServiceTypes as ServiceTypes
ON ServiceTypes.ServiceTypeID=Services.ServiceTypeID
INNER JOIN tm_Users as Users
ON Users.UserID=Services.EntityKeyID

and Services.StatusTypeID <>13
and ServiceTypes.ServiceTypeID IN ('9','7')
and Services.DateRequired BETWEEN @From and @To
)t

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 12:21:35

I want distinct UserID values from each query but I also want distinct values from the union. In other words, if a specific pilot(UserID) flys(query1) and also relocates(query2) I only want to count him once

thats what union does. it includes pilot only once

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stharish
Starting Member

43 Posts

Posted - 2012-01-10 : 12:32:57

I see. Then there must be something wrong with the data since I'm getting a count which is a couple off from actual.

Thanks again for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 12:35:40

quote:
Originally posted by stharish

I see. Then there must be something wrong with the data since I'm getting a count which is a couple off from actual.

Thanks again for the help.

one reason may be you're including some additional column in your select which you've not shown here and its having different values for same pilot for both queries. In that case UNION will count both as combination is distinct for both

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stharish
Starting Member

43 Posts

Posted - 2012-01-10 : 14:36:55

It's the data. Late night flights departing in the central time zone causes pilots to be counted the following day.

I just have to adjust the parameters.

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-10 : 14:59:54

Well late nights AFTER Midnight ARE the next day

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 01:17:08

quote:
Originally posted by stharish

It's the data. Late night flights departing in the central time zone causes pilots to be counted the following day.

I just have to adjust the parameters.

Thanks

try to reduce all of times in different timezones to common timezone while comparing. May be convert all of them to UTC time or something.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources