How can I combine the results of three queries into one dataset? Cross Apply or join?
create table tbl_A
(
Dept varchar(5),
DocCountA int
);
create table tbl_b
(
Dept varchar(5),
DocCountB int
);
create table tbl_c
(
Dept varchar(5),
DocCountC int
);
insert into tbl_A
values ('XX',12),('YY',14),('ZZ',16)
insert into tbl_b
values ('XX',20),('YY',25),('ZZ',27)
insert into tbl_c
values ('XX',30),('YY',35)
select * from tbl_A
select * from tbl_B
select * from tbl_C
**tbl_A**
Dept DocCountA
XX 12
YY 14
ZZ 16
**tbl_B**
Dept DocCountB
XX 20
YY 25
ZZ 27
**tbl_C**
Dept DocCountC
XX 30
YY 35
Result
Dept DocCountA DocCountB DocCountC
XX
YY
ZZ
Table C has two results ZZ will be blank
this works using two queries Three will be great!
select * from tbl_A A
cross apply
(
select B.DocCountB from tbl_b B
Where B.Dept=A.Dept
) sod
A left join on each table will allow you to get all 3 departments and the counts if they exist.
Here is a working fiddle