CODEWITHSUNDEEP
sqlsql-server
Artsom
Artsom

Reputation: 171

SQL query to get info from different tables

Need a help with sql query. I have 2 tables.

Matches.

    first_referee_arbitr_id
    second_referee_arbitr_id
    home_team
    guest_team
    date

Arbitrs.

    id
    name
    surname

first_referee_arbitr_id and second_referee_arbitr_id are from Arbitrs table(id).

As the result I need to count how much each referee has been included in matches:

   referee_first_count | referee_second_count | Arbitr Surname | Arbitr Name

My query now:

SELECT  T1.referee_first_count, T2.referee_second_count, T1.surname, T1.name
FROM (
    select matches.first_referee_arbitr_id, q.surname, q.name, count(*) AS referee_first_count
    FROM matches
    JOIN (
      SELECT name, surname, id
      FROM arbitrs
    ) as q ON matches.first_referee_arbitr_id=q.id
    GROUP BY matches.first_referee_arbitr_id, q.name, q.surname
    ORDER BY referee_first_count DESC
  ) T1 FULL OUTER JOIN
  (
    select matches.second_referee_arbitr_id, arbitr.surname, arbitr.name, count(*) AS referee_second_count
    FROM matches
    JOIN (
      SELECT name, surname, id
      FROM arbitrs
    ) as arbitr ON matches.second_referee_arbitr_id=arbitr.id
    GROUP BY matches.second_referee_arbitr_id, arbitr.name, arbitr.surname
    ORDER BY referee_second_count DESC
  ) T2
  ON (
  (T1.name = T2.name) AND (T1.surname = T2.surname)
  )

Example

Arbitrs:


 id | Name | Surname
  1   John    Rambler
  2   Steve   Crystler
  3    Tom    Ferguson

Matches:


 first_referee_arbitr_id| second_referee_arbitr_id| home_team | guest_team | date
         1                                            Chelsea     Everton     22.06.2020 
         2                          1                 Liverpool   Lester      28.06.2020 
         3                                            Dinamo K    Dinamo M    06.07.2020
         1                          2                 Juventus    Dinamo K    10.10.2020
         3                                            Dinamo K    Chelsea     20.20.2020

Result:


   referee_first_count | referee_second_count | surname | name
        2                       1                Rambler   John
        1                       1                Crystler  Steve
        2                       0                Ferguson  Tom

My result now with query above:

   referee_first_count | referee_second_count | surname | name
        2                       1                Rambler   John
        1                       1                Crystler  Steve
        2                       NULL                NULL      NULL

Upvotes: 0

Views: 51

Answers (2)

eshirvana
eshirvana

Reputation: 24568

SELECT  
  a.name
 , a.surname
 , COUNT(CASE WHEN m1.id IS NOT NULL THEN 1 end) referee_first_count
 , COUNT(CASE WHEN m2.id IS NOT NULL THEN 1 end) referee_second_count
FROM 
Arbitrs a
LEFT JOIN macthes  m1 
 ON a.id = m.first_referee_arbitr_id
LEFT JOIN macthes  m2
 ON a2.id = m.second_referee_arbitr_id
GROUP BY 
    a.name
    , a.surname
-- if you want to show only those with refrence
HAVING referee_first_count > 0 or referee_second_count > 0

Upvotes: 0

Wouter
Wouter

Reputation: 2976

First aggregate the matches table (twice) and left join this to the Arbitrs table:

SELECT a.name , a.Surname , ISNULL(fir.first_count,0) , ISNULL( sec.sec_count,0)
FROM Arbitrs a
LEFT JOIN (
        SELECT first_referee_arbitr_id,COUNT(*) as first_count
        FROM Matches
        GROUP BY first_referee_arbitr_id
        ) fir
        ON fir.first_referee_arbitr_id = a.id 
LEFT JOIN (
        SELECT second_referee_arbitr_id,COUNT(*) as sec_count
        FROM Matches
        GROUP BY second_referee_arbitr_id
        ) sec
        ON sec.second_referee_arbitr_id = a.id

Upvotes: 1

Related Questions