Manish_updated_sql_join, Sql Server

Manish_updated_sql_join

Language: Layout:

create table salesman (salesman_id char(6), name char(12), city char(20), commission decimal(2,2));
Insert into salesman values('5001', 'James Hoog', 'New York', 0.15)
Insert into salesman values('5002 ', 'Nail Knite', 'Paris', 0.13);
Insert into salesman values('5005 ', 'Pit Alex', 'london', 0.11);
Insert into salesman values('5003 ', 'Lauson Hen',' ', 0.12);
Insert into salesman values('5006 ', 'Mc Lyon ','Paris ', 0.14);
Insert into salesman values('5007 ', 'Paul Adam ','Romes ', 0.13);
Insert into salesman values('9007 ', 'Paul Adam ','Romes ', 0.13);
create table customer (customer_id char(6), cust_name char(12),    city char(20),        grade decimal(6),       salesman_id char(6));
Insert into customer values('3002',         'Nick Rimando',  'New York',    100,         '5001');
Insert into customer values('3005',         'Graham Zusi  ', 'California',  200,         '5002');
Insert into customer values('3001',         'Brad Guzan',    'London    ', 0     ,        '5005');
Insert into customer values('3004',         'Fabian Johns',  'Paris     ',  300,         '5006');
Insert into customer values('3001',         'Brad Guzan',    'London    ', 0     ,        '5005');
Insert into customer values('3001',         'Brad Guzan',    'London    ', 0     ,        '5005');
Insert into customer values('3009',         'Geoff Camero',  'Berlin    ',  100,         '5003');
Insert into customer values('3008',         'Julian Green',  'London    ',  300,         '5002');
Insert into customer values('3003',         'Jozy Altidor',  'Moscow    ',  200,         '5007');
Delete from customer where cust_name = '	Brad Guzan ';
Delete from customer where cust_name = 'Brad Guzan';
Insert into customer values('3001',         'Brad Guzan',    'London    ',  0    ,        '5005');
Insert into customer values('3007',         'Brad Davis ',   'New York  ',  200,         '5001');
create table order1 (ord_no decimal(7,0), purch_amt decimal(6,2),   ord_date date,    customer_id char(6),  salesman_id char(6));
Insert into order1 values(70001,      150.5,       '2012-10-05',   '3005',  '5002');
Insert into order1 values(70009,       270.65,      '2012-09-10',  '3001 ',        '5005');
Insert into order1 values(70002,      65.26,       '2012-10-05',   '3002 ',        '5001');
Insert into order1 values(70007,       948.5,       '2012-09-10',  '3005 ',        '5002');
Insert into order1 values(70004,      110.5,       '2012-08-17',   '3009 ',        '5003');
Insert into order1 values(70005,       2400.6,      '2012-07-27',  '3007 ',        '5001');
Insert into order1 values(70008,       5760,        '2012-09-10',  '3002 ',        '5001');
Insert into order1 values(70010,       1983.43,     '2012-10-10',  '3004 ',        '5006');
Insert into order1 values(70003,       2480.4,      '2012-10-10',  '3009 ',        '5003');
Insert into order1 values(70012,       250.45,      '2012-06-27',  '3008 ',        '5002');
Insert into order1 values(70011,       75.29 ,      '2012-08-17',  '3003 ',        '5007');
Insert into order1 values(70013,       3045.6,      '2012-04-25',  '3002 ',        '5001');
Insert into customer values('3050',         'BradGuzanNEW',    'London    ', 350     ,        '5005');
select customer.customer_id,customer.cust_name,isnull(cnt,0) as cnt1
 from customer left outer join (select customer_id ,count(*) cnt from 
  order1 group by customer_id) a on 				
 customer.customer_id = a.customer_id;
 
 select customer_id, cust_name, (select count(*) from order1 
   where order1.customer_id = customer.customer_id) as 
 num_ord from customer;
 
 select customer.cust_name, customer.customer_id,count(order1.ord_no)
	from customer left outer join order1 on customer.customer_id = 
    order1.customer_id group by customer.cust_name,customer.customer_id;

select customer.cust_name,customer.city,order1.ord_no,order1.ord_date, 
order1.purch_amt, salesman.name, salesman.commission 
from customer  left outer join order1 on customer.customer_id = order1.customer_id 
left outer join salesman on customer.salesman_id = salesman.salesman_id;

Insert into customer values('3060',         'TestGuzanNEW',    'London    ',
                            350     ,        ' ');

Select * from salesman;
Select * from Customer;
Select * from order1;
Select * from Salesman s 
Left join
    (Select c.customer_id, c.cust_name, c.salesman_id, o.ord_no from customer c 
    left join order1 o 
    on c.customer_id=o.customer_id and c.salesman_id = o.salesman_id)   a 
    
    On s.salesman_id = a.salesman_id;

Select s.salesman_Id, S.name, s.city, isnull(a.customer_ID, ' ') as 'Customer Id', isnull(a.cust_name, 'NoOrdByLinkdCust') as 'Cust Name', 
case isnull(a.Ord_no, 0)
when 0 then 'NoOrder'
End,
isnull(a.ord_no, 0) 'Order No', isnull(a.purch_amt,0) 'Purchase Amt'from Salesman s 
LEFT JOIN
(Select c.customer_id, c.cust_name, c.salesman_id, o.ord_no, o.purch_amt from customer c 
LEFT JOIN order1  o 
ON c.customer_id=o.customer_id and 
c.salesman_id = o.salesman_id ) a 
On s.salesman_id = a.salesman_id;

View schema
Execution time: 0,08 sec, rows selected: 106, rows affected: 35, absolute service time: 0,3 sec

	customer_id	cust_name	cnt1
1	3002	Nick Rimando	3
2	3005	Graham Zusi	2
3	3001	Brad Guzan	1
4	3004	Fabian Johns	1
5	3007	Brad Davis	1
6	3050	BradGuzanNEW	0
7	3009	Geoff Camero	2
8	3008	Julian Green	1
9	3003	Jozy Altidor	1

	customer_id	cust_name	num_ord
1	3002	Nick Rimando	3
2	3005	Graham Zusi	2
3	3001	Brad Guzan	1
4	3004	Fabian Johns	1
5	3007	Brad Davis	1
6	3050	BradGuzanNEW	0
7	3009	Geoff Camero	2
8	3008	Julian Green	1
9	3003	Jozy Altidor	1

	cust_name	customer_id	(No column name)
1	Brad Guzan	3001	1
2	Nick Rimando	3002	3
3	Jozy Altidor	3003	1
4	Fabian Johns	3004	1
5	Graham Zusi	3005	2
6	Brad Davis	3007	1
7	Julian Green	3008	1
8	Geoff Camero	3009	2
9	BradGuzanNEW	3050	0

	cust_name	city	ord_no	ord_date	purch_amt	name	commission
1	Nick Rimando	New York	70002	05.10.2012 00:00:00	65,26	James Hoog	0,15
2	Nick Rimando	New York	70008	10.09.2012 00:00:00	5760,00	James Hoog	0,15
3	Nick Rimando	New York	70013	25.04.2012 00:00:00	3045,60	James Hoog	0,15
4	Graham Zusi	California	70001	05.10.2012 00:00:00	150,50	Nail Knite	0,13
5	Graham Zusi	California	70007	10.09.2012 00:00:00	948,50	Nail Knite	0,13
6	Brad Guzan	London	70009	10.09.2012 00:00:00	270,65	Pit Alex	0,11
7	Fabian Johns	Paris	70010	10.10.2012 00:00:00	1983,43	Mc Lyon	0,14
8	Brad Davis	New York	70005	27.07.2012 00:00:00	2400,60	James Hoog	0,15
9	BradGuzanNEW	London	NULL	NULL	NULL	Pit Alex	0,11
10	Geoff Camero	Berlin	70004	17.08.2012 00:00:00	110,50	Lauson Hen	0,12
11	Geoff Camero	Berlin	70003	10.10.2012 00:00:00	2480,40	Lauson Hen	0,12
12	Julian Green	London	70012	27.06.2012 00:00:00	250,45	Nail Knite	0,13
13	Jozy Altidor	Moscow	70011	17.08.2012 00:00:00	75,29	Paul Adam	0,13

	salesman_id	name	city	commission
1	5001	James Hoog	New York	0,15
2	5002	Nail Knite	Paris	0,13
3	5005	Pit Alex	london	0,11
4	5003	Lauson Hen		0,12
5	5006	Mc Lyon	Paris	0,14
6	5007	Paul Adam	Romes	0,13
7	9007	Paul Adam	Romes	0,13

	customer_id	cust_name	city	grade	salesman_id
1	3002	Nick Rimando	New York	100	5001
2	3005	Graham Zusi	California	200	5002
3	3001	Brad Guzan	London	0	5005
4	3004	Fabian Johns	Paris	300	5006
5	3007	Brad Davis	New York	200	5001
6	3050	BradGuzanNEW	London	350	5005
7	3009	Geoff Camero	Berlin	100	5003
8	3008	Julian Green	London	300	5002
9	3003	Jozy Altidor	Moscow	200	5007
10	3060	TestGuzanNEW	London	350

	ord_no	purch_amt	ord_date	customer_id	salesman_id
1	70001	150,50	05.10.2012 00:00:00	3005	5002
2	70009	270,65	10.09.2012 00:00:00	3001	5005
3	70002	65,26	05.10.2012 00:00:00	3002	5001
4	70007	948,50	10.09.2012 00:00:00	3005	5002
5	70004	110,50	17.08.2012 00:00:00	3009	5003
6	70005	2400,60	27.07.2012 00:00:00	3007	5001
7	70008	5760,00	10.09.2012 00:00:00	3002	5001
8	70010	1983,43	10.10.2012 00:00:00	3004	5006
9	70003	2480,40	10.10.2012 00:00:00	3009	5003
10	70012	250,45	27.06.2012 00:00:00	3008	5002
11	70011	75,29	17.08.2012 00:00:00	3003	5007
12	70013	3045,60	25.04.2012 00:00:00	3002	5001

	salesman_id	name	city	commission	customer_id	cust_name	salesman_id	ord_no
1	5001	James Hoog	New York	0,15	3002	Nick Rimando	5001	70002
2	5001	James Hoog	New York	0,15	3002	Nick Rimando	5001	70008
3	5001	James Hoog	New York	0,15	3002	Nick Rimando	5001	70013
4	5001	James Hoog	New York	0,15	3007	Brad Davis	5001	70005
5	5002	Nail Knite	Paris	0,13	3005	Graham Zusi	5002	70001
6	5002	Nail Knite	Paris	0,13	3005	Graham Zusi	5002	70007
7	5002	Nail Knite	Paris	0,13	3008	Julian Green	5002	70012
8	5005	Pit Alex	london	0,11	3001	Brad Guzan	5005	70009
9	5005	Pit Alex	london	0,11	3050	BradGuzanNEW	5005	NULL
10	5003	Lauson Hen		0,12	3009	Geoff Camero	5003	70004
11	5003	Lauson Hen		0,12	3009	Geoff Camero	5003	70003
12	5006	Mc Lyon	Paris	0,14	3004	Fabian Johns	5006	70010
13	5007	Paul Adam	Romes	0,13	3003	Jozy Altidor	5007	70011
14	9007	Paul Adam	Romes	0,13	NULL	NULL	NULL	NULL

	salesman_Id	name	city	Customer Id	Cust Name	(No column name)	Order No	Purchase Amt
1	5001	James Hoog	New York	3002	Nick Rimando	NULL	70002	65,26
2	5001	James Hoog	New York	3002	Nick Rimando	NULL	70008	5760,00
3	5001	James Hoog	New York	3002	Nick Rimando	NULL	70013	3045,60
4	5001	James Hoog	New York	3007	Brad Davis	NULL	70005	2400,60
5	5002	Nail Knite	Paris	3005	Graham Zusi	NULL	70001	150,50
6	5002	Nail Knite	Paris	3005	Graham Zusi	NULL	70007	948,50
7	5002	Nail Knite	Paris	3008	Julian Green	NULL	70012	250,45
8	5005	Pit Alex	london	3001	Brad Guzan	NULL	70009	270,65
9	5005	Pit Alex	london	3050	BradGuzanNEW	NoOrder	0	0,00
10	5003	Lauson Hen		3009	Geoff Camero	NULL	70004	110,50
11	5003	Lauson Hen		3009	Geoff Camero	NULL	70003	2480,40
12	5006	Mc Lyon	Paris	3004	Fabian Johns	NULL	70010	1983,43
13	5007	Paul Adam	Romes	3003	Jozy Altidor	NULL	70011	75,29
14	9007	Paul Adam	Romes		NoOrdByLinkd	NoOrder	0	0,00

	salesman_Id	name	city	Customer Id	Cust Name	(No column name)	Order No	Purchase Amt
1	5001	James Hoog	New York	3002	Nick Rimando	NULL	70008	5760,00
2	5001	James Hoog	New York	3002	Nick Rimando	NULL	70013	3045,60
3	5001	James Hoog	New York	3007	Brad Davis	NULL	70005	2400,60
4	5002	Nail Knite	Paris		NoOrdByLinkd	NoOrder	0	0,00
5	5005	Pit Alex	london		NoOrdByLinkd	NoOrder	0	0,00
6	5003	Lauson Hen		3009	Geoff Camero	NULL	70003	2480,40
7	5006	Mc Lyon	Paris		NoOrdByLinkd	NoOrder	0	0,00
8	5007	Paul Adam	Romes		NoOrdByLinkd	NoOrder	0	0,00
9	9007	Paul Adam	Romes		NoOrdByLinkd	NoOrder	0	0,00