ALUFTW ALUFTW - 7 months ago 14
SQL Question

Unite two column tables together within SELECT

I have two select queries I made:

SELECT * FROM Fireman_Team WHERE Team_Code in (
SELECT Answer.Team_Code FROM Answer WHERE
Answer.Call_Number IN(
SELECT Call_Number FROM (SELECT * FROM Call WHERE Call.Time_Started
BETWEEN ('2016-01-01 00:00:00') AND
('2016-01-31 23:59:59')) AS SSS))


This code returns:

Team_Code ID
433 203994834
436 203994834
436 309228372
435 309228373
434 309228374
433 399064113


The second query is:

SELECT COUNT(Team_Code) as number_of_times,
Team_Code FROM (SELECT Answer.Team_Code FROM Answer WHERE
Answer.Call_Number IN(
SELECT Call_Number FROM (SELECT * FROM Call WHERE Call.Time_Started
BETWEEN ('2016-01-01 00:00:00') AND
('2016-01-31 23:59:59')) AS SSS)
) AS re GROUP BY Team_Code


which returns this table:

number_of_times Team_Code
3 433
2 434
1 435
1 436


I would like to unite these two tables together into one that would look like:

number_of_times Team_Code ID
3 433 203994834
2 434 309228374
1 435 309228373
1 436 203994834


... etc .. (Id can appear more than once)

The reason I'm doing this is that I want to find the ID that has the MAXIMUM
number_of_times (which means I will have to count the number_of_times
to every ID from the table I asked about).

Answer
; with 
query1 as
(
-- Your Query 1
SELECT * FROM Fireman_Team WHERE Team_Code in (
  SELECT Answer.Team_Code FROM Answer WHERE
  Answer.Call_Number IN(
    SELECT Call_Number FROM (SELECT * FROM Call WHERE Call.Time_Started
      BETWEEN ('2016-01-01 00:00:00') AND 
      ('2016-01-31 23:59:59')) AS SSS)) 
),
query2 as
(
-- Your Query 2
SELECT COUNT(Team_Code) as number_of_times, 
Team_Code FROM (SELECT Answer.Team_Code FROM Answer WHERE
  Answer.Call_Number IN(
    SELECT Call_Number FROM (SELECT * FROM Call WHERE Call.Time_Started
      BETWEEN ('2016-01-01 00:00:00') AND 
      ('2016-01-31 23:59:59')) AS SSS)
) AS re GROUP BY Team_Code
)
select *
from   query1 q1 inner join query2 q2 on q1.Team_Code = q2.Team_Code
Comments