Combine 2 queries? - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Combine 2 queries?

Author

Topic

QuattroDave
Starting Member

4 Posts

Posted - 2014-02-19 : 06:27:44

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

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-02-19 : 06:47:17

[code]

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'

[/code]

S

sabinWeb MCP

QuattroDave
Starting Member

4 Posts

Posted - 2014-02-19 : 07:12:28

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

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-02-19 : 07:13:24

You are welcome

S

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources