How do I combine the result below together with the result above with another column?

Question

question

juz410 asked Jun 8, '21

How do I combine the result below together with the result above with another column?

The ideal queryI want

SELECT Airline_ID,Airline_Name, (SELECT COUNT(Airline_ID) As Total_Number_of_Flight

FROM Flight

WHERE Airline_ID IN (SELECT Airline_ID From Airline)

GROUP BY Airline_ID) FROM Airline;
But it shows me that sub query cannot have more than 1 value

mssql

1623139999078.png (42.2 KiB)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2021-06-08T08:40:31Z

Kev Riley answered Jun 8, '21

Join the two tables and count the rows

select 
    Airline.Airline_ID, 
    Airline.Airline_Name,
    isnull(count(Flight.Airline_ID),0) As Total_Number_of_Flight
from Airline
left join Flight on Airline.Airline_ID= Flight.Airline_ID
group by  Airline.Airline_ID, Airline.Airline_Name

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

juz410 commented · Jun 08, 2021 at 10:44 AM

Wow thanks lot it's work but how is that logic work? I'm so confused on "JOIN"

0 ·

question