SQL QUERY - URGENT.. PLS HELP

Question

Hello All.... I am again stuck-up in making a query.... I tried making it.... Please guide further..

 

Please do let me know if any other data is required....... PLEASE HELP.... ITS URGENT

 

I am sharing my table-structures and the query...

 

Table Structure

1. Table Name – Tbl_Entity_Master

E_CODE	E_NAME	STATUS	ACTIVE	DISDATE	E_TYPE
100	ABC PvtLtd	Active	1	1-1-2018	Private
101	BCD Private Limited	InActive	Null	1-1-2018	Public
102	DEF Co. Ltd	Strike-Off with other things	1	1-1-2018	Individual
103	ABC Co. Ltd	Struck-Off with others	1	Null	Individual
104	BCD Co. td	Active	1	null	Public
105	GHI Co.	Amalgagted with others	1	Null	Individual
106	JKL Co.	Handed over to others	Null	Null	Public
107	MNO Pvt Ltd	Handed over	Null	Null	Individual
108	PQR Co. Ltd	Active	1	Null	Private
109	PQR Pvt Ltd	Active	1	Null	Public
110	ACB Co. Ltd	Active	1	Null	Private
111	XYZ Pvt Ltd	Active	1	Null	Public
112	IJK Co. Ltd	Active	Null	null	Private
113	KLM Pvt Ltd	Handed over	Null	Null	Individual
114	EFG PvtLtd	Handed over	Null	1-1-2018	Private
115	FGH Pvt LTd	Struck-Off	Null	1-1-2018	Public
116	RST Pvt Ltd	Active	1	Null	Private
117	XYZ Co. Ltd	Active	1	Null	Public
118	HIJ Pvt Ltd	Active	1	Null	Private
119	LMO Co. Ltd	Activie	1	Null	Private
120	MNO Co. Ltd	null	1	null	Public

2. TBL_ENTITY_DETAILS

ID	E_CODE	ACC_CODE	ACC_NAME	SHARES(in%)	CEO_CODE	gref
1	100	20	Rohit Joshi	8	10	Others
2	101	20	Rohit Joshi	8	10	Others
3	102	20	Rohit Joshi	8	10	Others
4	103	20	Rohit Joshi	8	10	Others
5	104	20	Rohit Joshi	8	10	Outside
6	105	20	Rohit Joshi	8	10	Outside
7	102	30	Vijay Shah	8	20	Others
8	103	30	Vijay Shah	8	20	Others
9	104	30	Vijay Shah	5	20	Others
10	105	30	Vijay Shah	5	20	Others
11	106	30	Vijay Shah	5	20	Others
12	107	30	Vijay Shah	5	20	Others
13	108	50	Malini Parikh	5	40	Others
14	109	50	Malini Parikh	5	40	Others
15	110	50	Malini Parikh	5	40	Others
16	109	50	Malini Parikh	5	40	Others
17	110	50	Malini Parikh	5	40	Others
18	111	50	Malini Parikh	6	30	Outside
19	112	20	Rohit Joshi	6	30	Others
20	113	30	Vijay Shah	6	30	Others
21	114	50	Malini Parikh	6	30	Others
22	115	60	Shruti Dave	6	30	Others
23	116	60	Shruti Dave	6	44	Others
24	117	60	Shruti Dave	6	44	Others
25	114	60	Shruti Dave	7	44	Others
26	115	20	Rohit Joshi	7	44	Others
27	116	20	Rohit Joshi	7	44	Others
28	117	30	Vijay Shah	7	55	Others
29	118	50	Malini Parikh	7	55	Others
30	119	50	Malini Parikh	7	55	Others
31	120	Select	NA	Null	55	Others
32	119	Select	NA	null	55	Others

3. TBL_CERTIFIED_ENTITIES

ID	E_CODE	MONTH	YEAR
1	100	MAY	2018
2	108	MAY	2018
3	109	MAY	2018
4	110	MAY	2018
5	111	NULL	NULL
6	112	NULL	NULL
7	116	MAY	2018
8	117	MAY	2018
9	118	MAY	2018
10	119	MAY	2018
11	120	NULL	NULL

Total Companies Query –

Select master.e_code, master.e_name

from tb_entity_master master, Tbl_entity_details details

Where master.e_code =details.e_code and

(Master.status =active or Master.Status is null)

AND (master.status not like ‘%Strike %’ or status is null)

AND (master.status not like ‘%Struck %’ or status is null)

AND (master.status not like ‘%Amalgated %’ or status is null)

AND (master.status not like ‘%Handed%’ or status is null)

AND (master.status <> ‘InActive’)

AND master.E_TYPE <> ‘Individual’

AND master.Active = 1

AND master.Disdate is null

AND details.gref <> ‘Outside’

AND details.ACC_CODE <>’Select’

Certified Companies Query

Select master.e_code, master.e_name , ‘Certified’ as CertifiedStatus

from tb_entity_master master, Tbl_entity_details details, TBL_Certified_Companies certified

Where master.e_code =details.e_code and

Master.e_code = certified.e_code and

(Master.status =active or Master.Status is null)

AND (master.status not like ‘%Strike %’ or status is null)

AND (master.status not like ‘%Struck %’ or status is null)

AND (master.status not like ‘%Amalgated %’ or status is null)

AND (master.status not like ‘%Handed%’ or status is null)

AND (master.status <> ‘InActive’)

AND master.E_TYPE <> ‘Individual’

AND master.Active = 1

AND master.Disdate is null

AND details.gref <> ‘Outside’

AND details.ACC_CODE <>’Select’

And certified.Month = ‘May’ AND certified.Year=2018

UNION

Select master.e_code, master.e_name , ‘UNCertified’ as CertifiedStatus

from tb_entity_master master, Tbl_entity_details details, TBL_Certified_Companies certified

Where master.e_code =details.e_code and

Master.e_code = certified.e_code and

(Master.status =active or Master.Status is null)

AND (master.status not like ‘%Strike %’ or status is null)

AND (master.status not like ‘%Struck %’ or status is null)

AND (master.status not like ‘%Amalgated %’ or status is null)

AND (master.status not like ‘%Handed%’ or status is null)

AND (master.status <> ‘InActive’)

AND master.E_TYPE <> ‘Individual’

AND master.Active = 1

AND master.Disdate is null

AND details.gref <> ‘Outside’

AND details.ACC_CODE <>’Select’

And certified.Month is null AND certified.Year is null

----------

Issue is - The total count of both the queries should be same. But, the count is coming different. I am not getting where I am going wrong.

Please guide further...