Need help on SQL query

[former_member10122]

Hi Team,

I have an SQL query to fetch the records from DB and then am doing further filtration to validate the records.

Below is the select statement and based on the records, I need to check whether Email id is valid against the domains maintained in DB by executing another SQL query and check duplicate emails as well as well, can all this be clubbed in the same select query ?

Please advise.

Email Domain query -> select MAILDOMAIN from arpt.dbo.AD_MAIL_DOMAINS_V

Duplicate Email check -> Select EMAIL from [arpt].[dbo].[E_AND_IP_V] GROUP BY EMAIL HAVING COUNT(*)>1

Select statement

select 
  SUBSTRING(a.ID, 1, 30) AS ID, 
  SUBSTRING(a.EMAIL, 1, 60) AS USERNAME, 
  a.EMAIL AS EMAIL, 
  SUBSTRING(a.FIRST_NAME, 1, 30) AS FIRSTNAME, 
  SUBSTRING(a.LAST_NAME, 1, 30) AS LASTNAME, 
  SUBSTRING(a.USER_TYPE, 1, 30) AS TRAVELLERGROUP, 
  SUBSTRING(
    CASE WHEN a.COUNTRY_CODE like 'CN' 
    AND a.USER_TYPE like 'Emp' THEN a.CONS_UNIT WHEN a.COUNTRY_CODE like 'IN' 
    AND a.USER_TYPE like 'Internal Partner' 
    and a.SUBSIDIARY_NAME like 'test' THEN 'IND' ELSE a.SUBSIDIARY_NAME END, 
    1, 
    30
  ) AS SUBSIDIARYNAME, 
  SUBSTRING(a.ENTITY_NAME, 1, 50) AS ENTITY, 
  SUBSTRING(a.SUB_ENTITY_NAME, 1, 50) AS SUB_ENTITY, 
  SUBSTRING(a.ENTERPRISE_NAME, 1, 50) AS ENTERPRISE, 
  a.HR_SOURCE_CODE AS HR_SOURCE_CODE, 
  APPROVER =(
    select 
      b.EMAIL 
    from 
      [arpt].[dbo].[E_AND_IP_MANAGER_V] b 
    where 
      b.STATUS like 'A' 
      and b.USERNAME = a.MANAGER_USERNAME 
    group by 
      b.EMAIL
  ), 
  SUBSTRING(a.TITLE, 1, 60) AS TITLE, 
  SUBSTRING(a.COUNTRY_CODE, 1, 2) AS COUNTRYCODE, 
  STATUS as STATUS 
from 
  [arpt].[dbo].[E_AND_IP_V] a 
where 
  a.STATUS like 'A' 
UNION 
select 
  SUBSTRING(a.ID, 1, 30) AS ID, 
  SUBSTRING(a.EMAIL, 1, 60) AS USERNAME, 
  a.EMAIL AS EMAIL, 
  SUBSTRING(a.FIRST_NAME, 1, 30) AS FIRSTNAME, 
  SUBSTRING(a.LAST_NAME, 1, 30) AS LASTNAME, 
  SUBSTRING(a.USER_TYPE, 1, 30) AS TRAVELLERGROUP, 
  SUBSTRING(
    CASE WHEN a.COUNTRY_CODE like 'IN' 
    AND a.USER_TYPE like 'Emp' THEN a.CONS_UNIT WHEN a.COUNTRY_CODE like 'IN' 
    AND a.USER_TYPE like 'Internal Partner' 
    and a.SUBSIDIARY_NAME like 'test' THEN 'IND' ELSE a.SUBSIDIARY_NAME END, 
    1, 
    30
  ) AS SUBSIDIARYNAME, 
  SUBSTRING(a.ENTITY_NAME, 1, 50) AS ENTITY, 
  SUBSTRING(a.SUB_ENTITY_NAME, 1, 50) AS SUB_ENTITY, 
  SUBSTRING(a.ENTERPRISE_NAME, 1, 50) AS ENTERPRISE, 
  a.HR_SOURCE_CODE AS HR_SOURCE_CODE, 
  APPROVER =(
    select 
      b.EMAIL 
    from 
      [arpt].[dbo].[E_AND_IP_MANAGER_V] b 
    where 
      b.STATUS like 'A' 
      and b.USERNAME = a.MANAGER_USERNAME 
    group by 
      b.EMAIL
  ), 
  SUBSTRING(a.TITLE, 1, 60) AS TITLE, 
  SUBSTRING(a.COUNTRY_CODE, 1, 2) AS COUNTRYCODE, 
  STATUS as STATUS 
from 
  [arpt].[dbo].[E_AND_IP_V] a 
where 
  a.STATUS like 'I'