Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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?
 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
Aged Yak Warrior

Romania
545 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
Aged Yak Warrior

Romania
545 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  
 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.86 seconds. Powered By: Snitz Forums 2000