25112
asked on
simplify EXISTS query
is this query in its most simplied form?
()a is a big UNION (the code goes between ())
and b() is a simpler query with conditions
the below logic is perfect.. but not sure how efficient it is... can you review it please?
select * from
() a /*a - this has 7 big unions within the () */
where not exists
(select 0 from ()b where a.key1=b.key1 and a.key2 = b.key2) /*b- This is a two table join with 3 conditions */
()a is a big UNION (the code goes between ())
and b() is a simpler query with conditions
the below logic is perfect.. but not sure how efficient it is... can you review it please?
select * from
() a /*a - this has 7 big unions within the () */
where not exists
(select 0 from ()b where a.key1=b.key1 and a.key2 = b.key2) /*b- This is a two table join with 3 conditions */
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It would definitely help to see the existing query and some sample data.
ASKER
will get the query and sample data in the morning. thx.
ASKER
the below is "b"
select distinct NIANE_ID,
right(LTRIM(RTRIM(cast('00 0'+cast(Ta gNumber as char(4)) as VARCHAR(10)))),4) TagNumber
from NianeStatHist join PotScore on PotScorePK=PotScoreFK
where statCd=20 and biddate is null
and TagNumber <>12
-----------------------
the final line conditions are
where FAI_KEY=NIANE_ID and Tag_ID = TagNumber)
-----------------------
and 'a' is:
select NIANE_ID FAI_KEY, right(right(LTRIM(RTRIM(ca st('000'+c ast(TagNum ber as char(4)) as VARCHAR(10)))),4),4) Tag_ID, '2013-01-01' TAG_YEAR , CASE WHEN min(TAG_StartDt)< '2013-01-01' THEN '2013-01-01' ELSE min(TAG_StartDt) END REPORT_TAG_DT,
CASE TagCode.BarTag
WHEN 'A01' THEN 'A001'
WHEN 'A02' THEN 'A002'
WHEN 'A03' THEN 'A003'
WHEN 'A04' THEN 'A004'
WHEN 'A05' THEN 'A005'
WHEN 'A06' THEN 'A006'
WHEN 'A07' THEN 'A007'
WHEN 'A08' THEN 'A008'
WHEN 'A09' THEN 'A009'
WHEN 'A010' THEN 'A0010'
WHEN 'A011' THEN 'A0011'
WHEN 'A012' THEN 'A0012'
WHEN 'A013' THEN 'A0013'
WHEN 'A014' THEN 'A0014'
WHEN 'A015' THEN 'A0015'
ELSE TagCode.BarTag END BarTag_Cd
from NianeGrd join PotScore on PotScorePK=PotScoreFK
join TagCode on TagCodeFK = TagCodePK
join NianeHist on PotScorePK=NianeHist.PotSc oreFK
join NianeCd on NianeCdPK = NianeCdFK
where BasicStart is null and PitsInNiane<=1997 and TagCode.BarTag IN ('A01', 'A02','A03','A04','A05','A 06','A07', 'A08','A09 ','A010',' A011','A01 2','A0015' ,'A0013',' A0014') and NianeCdPK in
( 7, 8, 3, 1, 5 )
GROUP by NIANE_ID, TagNumber, TagCode.BarTag
union
select DISTINCT NIANE_ID FAI_KEY, right(LTRIM(RTRIM(cast('00 0'+cast(Ta gNumber as char(4)) as VARCHAR(10)))),4) Tag_ID, '2014-06-30' TAG_YEAR , CASE WHEN min(TAG_StartDt)< '2013-07-01' THEN '2013-07-01' ELSE min(TAG_StartDt) END REPORT_TAG_DT,
'K5A' BarTag_Cd
from NianeGrd join PotScore on PotScorePK=PotScoreFK
join TagCode on TagCodeFK = TagCodePK
join NianeHist on PotScorePK=NianeHist.PotSc oreFK
join NianeCd on NianeCdPK = NianeCdFK
where BasicStart is null and PitsInNiane<=1997 and TagCode.BarTag IN ('K5H') and NianeCdPK in
( 7, 8, 3, 1, 5 )
GROUP by NIANE_ID, TagNumber, TagCode.BarTag
UNION
select DISTINCT NIANE_ID FAI_KEY,right(LTRIM(RTRIM( cast('000' +cast(TagN umber as char(4)) as VARCHAR(10)))),4) Tag_ID, '2014-06-30' TAG_YEAR , CASE WHEN min(TAG_StartDt)< '2013-07-01' THEN '2013-07-01' ELSE min(TAG_StartDt) END REPORT_TAG_DT,
'K5P' BarTag_Cd
from NianeGrd join PotScore on PotScorePK=PotScoreFK
join TagCode on TagCodeFK = TagCodePK
join NianeHist on PotScorePK=NianeHist.PotSc oreFK
join NianeCd on NianeCdPK = NianeCdFK
where BasicStart is null and PitsInNiane<=1997 and TagCode.BarTag IN ('K5H') and NianeCdPK in
( 7, 8, 3, 1, 5 )
GROUP by NIANE_ID, TagNumber, TagCode.BarTag
union
select DISTINCT NIANE_ID FAI_KEY, right(LTRIM(RTRIM(cast('00 0'+cast(Ta gNumber as char(4)) as VARCHAR(10)))),4) Tag_ID, '2014-06-30' TAG_YEAR , CASE WHEN min(TAG_StartDt)< '2013-07-01' THEN '2013-07-01' ELSE min(TAG_StartDt) END REPORT_TAG_DT,
'K4A' BarTag_Cd
from NianeGrd join PotScore on PotScorePK=PotScoreFK
join TagCode on TagCodeFK = TagCodePK
join NianeHist on PotScorePK=NianeHist.PotSc oreFK
join NianeCd on NianeCdPK = NianeCdFK
where BasicStart is null and PitsInNiane<=1997 and TagCode.BarTag IN ('K4H') and NianeCdPK in
( 7, 8, 3, 1, 5 )
GROUP by NIANE_ID, TagNumber, TagCode.BarTag
UNION
select DISTINCT NIANE_ID FAI_KEY, right(LTRIM(RTRIM(cast('00 0'+cast(Ta gNumber as char(4)) as VARCHAR(10)))),4) Tag_ID, '2014-06-30' TAG_YEAR , CASE WHEN min(TAG_StartDt)< '2013-07-01' THEN '2013-07-01' ELSE min(TAG_StartDt) END REPORT_TAG_DT,
'K4P' BarTag_Cd
from NianeGrd join PotScore on PotScorePK=PotScoreFK
join TagCode on TagCodeFK = TagCodePK
join NianeHist on PotScorePK=NianeHist.PotSc oreFK
join NianeCd on NianeCdPK = NianeCdFK
where BasicStart is null and PitsInNiane<=1997 and TagCode.BarTag IN ('K4H') and NianeCdPK in
( 7, 8, 3, 1, 5 )
GROUP by NIANE_ID, TagNumber, TagCode.BarTag
union
select DISTINCT NIANE_ID FAI_KEY, right(LTRIM(RTRIM(cast('00 0'+cast(Ta gNumber as char(4)) as VARCHAR(10)))),4) Tag_ID, '2014-06-30' TAG_YEAR , CASE WHEN min(TAG_StartDt)< '2013-07-01' THEN '2013-07-01' ELSE min(TAG_StartDt) END REPORT_TAG_DT,
'PKA' BarTag_Cd
from NianeGrd join PotScore on PotScorePK=PotScoreFK
join TagCode on TagCodeFK = TagCodePK
join NianeHist on PotScorePK=NianeHist.PotSc oreFK
join NianeCd on NianeCdPK = NianeCdFK
where BasicStart is null and PitsInNiane<=1997 and TagCode.BarTag IN ('PreKH') and NianeCdPK in
( 7, 8, 3, 1, 5 )
GROUP by NIANE_ID, TagNumber, TagCode.BarTag
UNION
select DISTINCT NIANE_ID FAI_KEY, right(LTRIM(RTRIM(cast('00 0'+cast(Ta gNumber as char(4)) as VARCHAR(10)))),4) Tag_ID, '2014-06-30' TAG_YEAR , CASE WHEN min(TAG_StartDt)< '2013-07-01' THEN '2013-07-01' ELSE min(TAG_StartDt) END REPORT_TAG_DT,
'PKP' BarTag_Cd
from NianeGrd join PotScore on PotScorePK=PotScoreFK
join TagCode on TagCodeFK = TagCodePK
join NianeHist on PotScorePK=NianeHist.PotSc oreFK
join NianeCd on NianeCdPK = NianeCdFK
where BasicStart is null and PitsInNiane<=1997 and TagCode.BarTag IN ('PreKH') and NianeCdPK in
( 7, 8, 3, 1, 5 )
GROUP by NIANE_ID, TagNumber, TagCode.BarTag
select distinct NIANE_ID,
right(LTRIM(RTRIM(cast('00
from NianeStatHist join PotScore on PotScorePK=PotScoreFK
where statCd=20 and biddate is null
and TagNumber <>12
-----------------------
the final line conditions are
where FAI_KEY=NIANE_ID and Tag_ID = TagNumber)
-----------------------
and 'a' is:
select NIANE_ID FAI_KEY, right(right(LTRIM(RTRIM(ca
CASE TagCode.BarTag
WHEN 'A01' THEN 'A001'
WHEN 'A02' THEN 'A002'
WHEN 'A03' THEN 'A003'
WHEN 'A04' THEN 'A004'
WHEN 'A05' THEN 'A005'
WHEN 'A06' THEN 'A006'
WHEN 'A07' THEN 'A007'
WHEN 'A08' THEN 'A008'
WHEN 'A09' THEN 'A009'
WHEN 'A010' THEN 'A0010'
WHEN 'A011' THEN 'A0011'
WHEN 'A012' THEN 'A0012'
WHEN 'A013' THEN 'A0013'
WHEN 'A014' THEN 'A0014'
WHEN 'A015' THEN 'A0015'
ELSE TagCode.BarTag END BarTag_Cd
from NianeGrd join PotScore on PotScorePK=PotScoreFK
join TagCode on TagCodeFK = TagCodePK
join NianeHist on PotScorePK=NianeHist.PotSc
join NianeCd on NianeCdPK = NianeCdFK
where BasicStart is null and PitsInNiane<=1997 and TagCode.BarTag IN ('A01', 'A02','A03','A04','A05','A
( 7, 8, 3, 1, 5 )
GROUP by NIANE_ID, TagNumber, TagCode.BarTag
union
select DISTINCT NIANE_ID FAI_KEY, right(LTRIM(RTRIM(cast('00
'K5A' BarTag_Cd
from NianeGrd join PotScore on PotScorePK=PotScoreFK
join TagCode on TagCodeFK = TagCodePK
join NianeHist on PotScorePK=NianeHist.PotSc
join NianeCd on NianeCdPK = NianeCdFK
where BasicStart is null and PitsInNiane<=1997 and TagCode.BarTag IN ('K5H') and NianeCdPK in
( 7, 8, 3, 1, 5 )
GROUP by NIANE_ID, TagNumber, TagCode.BarTag
UNION
select DISTINCT NIANE_ID FAI_KEY,right(LTRIM(RTRIM(
'K5P' BarTag_Cd
from NianeGrd join PotScore on PotScorePK=PotScoreFK
join TagCode on TagCodeFK = TagCodePK
join NianeHist on PotScorePK=NianeHist.PotSc
join NianeCd on NianeCdPK = NianeCdFK
where BasicStart is null and PitsInNiane<=1997 and TagCode.BarTag IN ('K5H') and NianeCdPK in
( 7, 8, 3, 1, 5 )
GROUP by NIANE_ID, TagNumber, TagCode.BarTag
union
select DISTINCT NIANE_ID FAI_KEY, right(LTRIM(RTRIM(cast('00
'K4A' BarTag_Cd
from NianeGrd join PotScore on PotScorePK=PotScoreFK
join TagCode on TagCodeFK = TagCodePK
join NianeHist on PotScorePK=NianeHist.PotSc
join NianeCd on NianeCdPK = NianeCdFK
where BasicStart is null and PitsInNiane<=1997 and TagCode.BarTag IN ('K4H') and NianeCdPK in
( 7, 8, 3, 1, 5 )
GROUP by NIANE_ID, TagNumber, TagCode.BarTag
UNION
select DISTINCT NIANE_ID FAI_KEY, right(LTRIM(RTRIM(cast('00
'K4P' BarTag_Cd
from NianeGrd join PotScore on PotScorePK=PotScoreFK
join TagCode on TagCodeFK = TagCodePK
join NianeHist on PotScorePK=NianeHist.PotSc
join NianeCd on NianeCdPK = NianeCdFK
where BasicStart is null and PitsInNiane<=1997 and TagCode.BarTag IN ('K4H') and NianeCdPK in
( 7, 8, 3, 1, 5 )
GROUP by NIANE_ID, TagNumber, TagCode.BarTag
union
select DISTINCT NIANE_ID FAI_KEY, right(LTRIM(RTRIM(cast('00
'PKA' BarTag_Cd
from NianeGrd join PotScore on PotScorePK=PotScoreFK
join TagCode on TagCodeFK = TagCodePK
join NianeHist on PotScorePK=NianeHist.PotSc
join NianeCd on NianeCdPK = NianeCdFK
where BasicStart is null and PitsInNiane<=1997 and TagCode.BarTag IN ('PreKH') and NianeCdPK in
( 7, 8, 3, 1, 5 )
GROUP by NIANE_ID, TagNumber, TagCode.BarTag
UNION
select DISTINCT NIANE_ID FAI_KEY, right(LTRIM(RTRIM(cast('00
'PKP' BarTag_Cd
from NianeGrd join PotScore on PotScorePK=PotScoreFK
join TagCode on TagCodeFK = TagCodePK
join NianeHist on PotScorePK=NianeHist.PotSc
join NianeCd on NianeCdPK = NianeCdFK
where BasicStart is null and PitsInNiane<=1997 and TagCode.BarTag IN ('PreKH') and NianeCdPK in
( 7, 8, 3, 1, 5 )
GROUP by NIANE_ID, TagNumber, TagCode.BarTag
ASKER
result:
10051 0744 2013-01-01 2013-01-01 00:00:00.000 A001
10051 0744 2013-01-01 2013-01-01 00:00:00.000 A007
10126 1216 2013-01-01 2013-01-01 00:00:00.000 A005
10126 1216 2013-01-01 2013-01-01 00:00:00.000 A0015
10127 0397 2013-01-01 2013-01-01 00:00:00.000 A0013
10127 4003 2013-01-01 2013-01-01 00:00:00.000 A001
10127 4003 2013-01-01 2013-01-01 00:00:00.000 A0010
10051 0744 2013-01-01 2013-01-01 00:00:00.000 A001
10051 0744 2013-01-01 2013-01-01 00:00:00.000 A007
10126 1216 2013-01-01 2013-01-01 00:00:00.000 A005
10126 1216 2013-01-01 2013-01-01 00:00:00.000 A0015
10127 0397 2013-01-01 2013-01-01 00:00:00.000 A0013
10127 4003 2013-01-01 2013-01-01 00:00:00.000 A001
10127 4003 2013-01-01 2013-01-01 00:00:00.000 A0010
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
king, your 2 points are well taken.. the result set is based on the combination of the whole query.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>the result set is based on the combination of the whole query.<<
Including the not exists clause? If so, can you post the results from just your "b" table join?
Including the not exists clause? If so, can you post the results from just your "b" table join?
ASKER