SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Combine 2 queries?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

QuattroDave
Starting Member

United Kingdom
4 Posts

Posted - 02/19/2014 :  06:27:44  Show Profile  Reply with Quote
Morning all,

I'm rather new to SQL, I've bumbled my way through 2 queries which both work in they're own right, however I would like to combine them into a single query but no idea where to start, I've done a bit of googling and reading up but I'm just getting more confused...

First query:

SELECT Bookings.B_BID, 
       Teachers.T_TID,
       (Teachers.T_Title + ' ' + 
       Teachers.T_FName + ' ' + 
       Teachers.T_LName) AS Name, 
       Teachers.T_Address1, 
       Teachers.T_Address2, 
       Teachers.T_Address3, 
       Teachers.T_Address4, 
       Teachers.T_Postcode, 
       Teachers.T_Phone_LL, 
       Teachers.T_Phone_Mobile, 
       Teachers.T_JobGroup    
FROM dbo.Bookings
  INNER JOIN dbo.Teachers
  ON Bookings.B_JobGroup = Teachers.T_JobGroup
WHERE Bookings.B_BID = '1001'
AND Teachers.T_Status = 'Live'
AND Teachers.T_JobGroup = 'Teacher Secondary'


Second query:

SELECT TOP 1 A_Colour FROM Availability
WHERE A_TID = '1016'  
AND A_AvailDate >= '2014-03-03'
AND A_AvailDate <= '2014-03-07'
GROUP BY A_Colour
ORDER BY COUNT(*) Desc


Essentially how I imagine it working is for each teacher returned by the first query, run the second query to determine the teachers availability...?

Hope that makes sense....

Thanks

Dave

EDIT:

The link between the 2 tables is Teachers.T_TID = Availability.A_TID

Edited by - QuattroDave on 02/19/2014 06:30:16

stepson
Constraint Violating Yak Guru

Romania
277 Posts

Posted - 02/19/2014 :  06:47:17  Show Profile  Reply with Quote


SELECT Bookings.B_BID, 
       Teachers.T_TID,
       (Teachers.T_Title + ' ' + 
       Teachers.T_FName + ' ' + 
       Teachers.T_LName) AS Name, 
       Teachers.T_Address1, 
       Teachers.T_Address2, 
       Teachers.T_Address3, 
       Teachers.T_Address4, 
       Teachers.T_Postcode, 
       Teachers.T_Phone_LL, 
       Teachers.T_Phone_Mobile, 
       Teachers.T_JobGroup  
	   ,A.*  
FROM dbo.Bookings
  INNER JOIN dbo.Teachers
  ON Bookings.B_JobGroup = Teachers.T_JobGroup

  OUTER APPLY
  (
	SELECT TOP 1 A_Colour 
	FROM Availability
	WHERE A_TID = T_TID
			AND A_AvailDate >= '2014-03-03'
			AND A_AvailDate <= '2014-03-07'
	GROUP BY A_Colour
	ORDER BY COUNT(*) Desc
  )A
WHERE Bookings.B_BID = '1001'
AND Teachers.T_Status = 'Live'
AND Teachers.T_JobGroup = 'Teacher Secondary'




S




sabinWeb MCP
Go to Top of Page

QuattroDave
Starting Member

United Kingdom
4 Posts

Posted - 02/19/2014 :  07:12:28  Show Profile  Reply with Quote
Hey Stepson,

Perfect thank you! Now that's a new command for me OUTER APPLY, I will do some reading up this afternoon.

Many thanks

Dave
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
277 Posts

Posted - 02/19/2014 :  07:13:24  Show Profile  Reply with Quote
You are welcome

S


sabinWeb MCP
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000