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
Go to Top of Page

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
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-02-19 : 07:13:24
You are welcome

S


sabinWeb MCP
Go to Top of Page
   

- Advertisement -