Use Count function in a left join : Table Join « Table Joins « SQL Server / T-SQL





Use Count function in a left join


1>
2> create table employee(
3>     ID          int,
4>     name        nvarchar (10),
5>     salary      int )
6> GO
1>
2> create table job(
3>     ID              int,
4>     title nvarchar  (10),
5>     averageSalary   int)
6> GO
1>
2>
3> insert into employee (ID, name, salary) values (1,  'Jason', 1234)
4> GO
1> insert into employee (ID, name, salary) values (2,  'Robert', 4321)
2> GO
1> insert into employee (ID, name, salary) values (3,  'Celia', 5432)
2> GO
1> insert into employee (ID, name, salary) values (4,  'Linda', 3456)
2> GO
1> insert into employee (ID, name, salary) values (5,  'David', 7654)
2> GO
1> insert into employee (ID, name, salary) values (6,  'James', 4567)
2> GO
1> insert into employee (ID, name, salary) values (7,  'Alison', 8744)
2> GO
1> insert into employee (ID, name, salary) values (8,  'Chris', 9875)
2> GO
1> insert into employee (ID, name, salary) values (9,  'Mary', 2345)
2> GO
1>
2> insert into job(ID, title, averageSalary) values(1,'Developer',3000)
3> GO
1> insert into job(ID, title, averageSalary) values(2,'Tester', 4000)
2> GO
1> insert into job(ID, title, averageSalary) values(3,'Designer', 5000)
2> GO
1> insert into job(ID, title, averageSalary) values(4,'Programmer', 6000)
2> GO
1>
2>
3> select * from employee;
4> GO
ID          name       salary
----------- ---------- -----------
          1 Jason             1234
          2 Robert            4321
          3 Celia             5432
          4 Linda             3456
          5 David             7654
          6 James             4567
          7 Alison            8744
          8 Chris             9875
          9 Mary              2345
1> select * from job;
2> GO
ID          title      averageSalary
----------- ---------- -------------
          1 Developer           3000
          2 Tester              4000
          3 Designer            5000
          4 Programmer          6000
1>
2> SELECT count(e.id), j.title
3> FROM Employee e LEFT JOIN job j
4> ON e.ID = j.ID
5> group by j.title
6> ORDER BY j.title
7> GO
            title
----------- ----------
          5 NULL
          1 Designer
          1 Developer
          1 Programmer
          1 Tester
1>
2>
3>
4> drop table employee;
5> drop table job;
6> GO
1>
2>
           
       










Related examples in the same category

1.Joining a Table with Itself
2.SQL Server join syntax
3.SET SHOWPLAN_TEXT ON for a table join
4.Joins are advantageous over subqueries if the SELECT list in a query contains columns from more than one table
5.Table join with data returned from function
6.NOT EXISTS function with table join
7.Select columns from all tables
8.Column alias in table join
9.Using TOP in table join