Join the 3 Tables with Different Count of Rows

Transact-SQL
21

to come in one recordset

you can do union all

select from head
union all
select QUERY

only thing number of columns and data types
have to match between " select from head " and "select QUERY "

22

Sir,

Sorry sir, I am not getting.

First the query mentioned in my post is OK????? Because I had added Head Name by joining Tbl_Head Table using Left Join

Second, I wish to join another one table called Tbl_Employee table with the following fields Emp_Code,Emp_Name,Place_Office,Designation,Scale,PAN,DOJ,Pay_Level,GPF_No,PRAN . Please help. Sorry for giving trouble. Can you change my query by adding the above table please.

Thanks & Regards,

23

right now i am busy

will take some time for to look
maybe 1 day

sorry

24

Sorry sir.

25

Thanks Sir. I will try and let you know sir..

Thanks Sir.

26 
SELECT a.emp_code,
	   h1.Head_Name, 
       a.FSE_Amount, 
       h2.head_name, 
       b.fsd_amount, 
       h3.head_name, 
       c.fsed_amount 
FROM   tbl_fse a left join Tbl_Head h1 on a.Head_ID=h1.Head_ID 
       FULL JOIN tbl_fsd b 
              ON a.sle_id = b.sld_id left join Tbl_Head h2 on b.Head_ID=h2.Head_ID
       FULL JOIN tbl_fsed c 
              ON c.sled_id = b.sld_id left join Tbl_Head h3 on c.Head_ID=h3.Head_ID

Sir, The above query is working Fine. But problem is I have to add condition. If I add condition as follows;

SELECT a.emp_code,
	   h1.Head_Name, 
       a.FSE_Amount,
       b.Emp_Code, 
       h2.head_name, 
       b.fsd_amount,
       b.Term,
       b.Settlement,
       c.Emp_Code, 
       h3.head_name, 
       c.fsed_amount 
FROM   tbl_fse a left join Tbl_Head h1 on a.Head_ID=h1.Head_ID 
       FULL JOIN tbl_fsd b 
              ON a.sle_id = b.sld_id left join Tbl_Head h2 on b.Head_ID=h2.Head_ID
       FULL JOIN tbl_fsed c 
              ON c.sled_id = b.sld_id left join Tbl_Head h3 on c.Head_ID=h3.Head_ID where a.Emp_Code='P-160' and a.Sal_Month='2018-11-01'

Showing data based on Table 1 Count. Now only I got happy suddenly it is vanished sir. Please fix and give me relax sir.

27

try this

SELECT a.emp_code,
h1.Head_Name,
a.FSE_Amount,
b.Emp_Code,
h2.head_name,
b.fsd_amount,
b.Term,
b.Settlement,
c.Emp_Code,
h3.head_name,
c.fsed_amount
FROM tbl_fse a full join Tbl_Head h1 on a.Head_ID=h1.Head_ID
FULL JOIN tbl_fsd b
ON a.sle_id = b.sld_id left join Tbl_Head h2 on b.Head_ID=h2.Head_ID
FULL JOIN tbl_fsed c
ON c.sled_id = b.sld_id left join Tbl_Head h3 on c.Head_ID=h3.Head_ID where a.Emp_Code='P-160' and a.Sal_Month='2018-11-01'

28

Sorry sir...same output...Finally lock sir...Definitely I have to place condition sir...Head is rotating sir..

30

what do you mean by

apply condition ???
same exercise ????

31

Sir, actually i have to take the report by employee wise and month wise. For testing purpose only, i had added fields for an employee of "P-160" . If I saved values for 500 employees how can I apply condition in our query like emp_code='S-123'.....Or emp_code='R-456' in our query sir.

And data will store every month, so I have to extract month wise report like sal_month='2019-01-01'. Or '2019-02-01' .......

That's why sir. Please help me to put condition in our query...

32

I am not able to understand what you are saying

33

Hi is it possible to have interaction
Some. Other place.

Do you have WhatsApp ???

35

something like this

the SQL also depends on data 
.. might  also need to 
ADD  filter by year

SELECT b.emp_code,
a.head_name,
b.head_name,
b.fsd_amount,
c.head_name,
c.fsed_amount,
c.sal_month
FROM tbl_fse a
FULL JOIN tbl_fsd b
ON a.sle_id = b.sld_id
FULL JOIN tbl_fsed c
ON c.sled_id = b.sld_id
WHERE b.emp_code = 'P-160' and MONTH(c.sal_month) = 11
go

WHERE b.emp_code = 'P-160'   and MONTH(c.sal_month) = 11  

here month = 11 means November 

1 = jan 
2 = feb 
....
10 = oct 
11 = nov 
12 = dec
36

Kind Attn to Shri.Harish

Sir, I had modified the query which you have sent it to me like as follows;

select EMPCODE,SALMONTH,FSEHN,FSE_Amount,FSDHN,FSD_Amount,Term,Settlement,FSEDHN,FSED_Amount from
(
select 
	Coalesce(E.Sal_Month, D.Sal_Month, ED.Sal_Month) As SALMONTH,
	Coalesce(E.emp_code, D.emp_code, ED.emp_code) As EMPCODE,
	h1.Head_Name FSEHN,
	E.FSE_Amount,
	h2.Head_Name FSDHN,
	D.fsd_amount,
	D.Term,
	D.Settlement,
	h3.Head_Name FSEDHN,
	ED.fsed_amount
From tbl_fse E left join Tbl_Head h1 on E.Head_ID=h1.Head_ID
Full Join tbl_fsd D On E.sle_id = D.sld_id left join Tbl_Head h2 on D.Head_ID=h2.Head_ID
Full Join tbl_fsed ED On E.sle_id = ED.sled_id
	And D.sld_id = ED.sled_id left join Tbl_Head h3 on ED.Head_ID=h3.Head_ID) fd join Tbl_Employee te on fd.EMPCODE=te.Emp_Code where te.Emp_Code='P-160' and fd.SALMONTH='2018-11-01'

Please have a look and advise sir.

Thanks

37

Sir,

Please refer my early reply;

The above query is working if only one employee. But when I add data for 500 employees, I have to apply condition otherwise it is throwing around 20 Lakhs rows.

Please help me for placing condition in the above query like emp_code='P-160' and Sal_Month='2018-11-01'' bu without affected the returns rows.

Thanks