quote:

---

Originally posted by mymatrix

Just to clarify further:
Implemented code may have equi-joins and inner joins to query the same table with multiple aliases.

For example:
This will return total units status wise with date criteria.

select (select sum(b.units) from tab1 as b where b.status = a.status and b.scheduledate>getdate()) as 'TotalUnits', a.Status
from tab1 as a
group by a.Status



thnks
Gaurav

Even my blood group says be -ve to all the negatives.

---

select  sum(case when scheduledate>getdate() then units else 0 end)  as 'TotalUnits', Status
from tab1 
group by Status

quote:

---

Originally posted by mymatrix

I agree that subquery is not required.
I have used subquery just to provide example of using multiple alias names.

Though i should have stated some other better query. thanks anyways.


**************************************

Even my blood group says be -ve to all the negatives.

---

Select
 xxxxxx        
from EveReq er join        
StatusMaster es on es.StatusID = er.StatusID        
left outer join EveMaster em on em.MasterID=er.EventType
left outer join EveMaster em2 on em2.MasterID=er.ActivityType        
left outer join EveMaster em3 on em3.MasterID=er.Area             
left outer join EveMaster em6 on em6.MasterID=er.PrimaryTechnology        
left outer join EveMaster em7 on em7.MasterID=er.AudienceType        
left outer join EveMaster em8 on em8.MasterID=er.Architecture        
left outer join EveMaster em9 on em9.MasterID=er.VerticalFocus        
left outer join EveMaster em10 on em10.MasterID=er.EventStakeHolders        
left outer join EveMaster em11 on em11.MasterID=er.PreferredFormofPayment        
left outer join EveMaster em12 on em12.MasterID=er.TemplateOptions        
left outer join EveMaster em13 on em13.MasterID=er.CopyBlocks        
left outer join EveMaster em14 on em14.MasterID=er.CISCOPhotos        
left outer join EveMaster em17 on em17.MasterID=er.VenueStatus        
left outer join EveMaster em18 on em18.MasterID=er.TechnicalRating        
left outer join EveMaster em19 on em19.MasterID=er.Vendor           
left outer join EveMaster em22 on em22.MasterID=er.SetupType        
left outer join EveMaster em23 on em23.MasterID=er.WhoShouldAttend        
left outer join UserMaster um1 on er.SalesRegion = um1.SalesRegionID        
 and er.FMMgr = um1.EmailAddress        
left outer join UserMaster um2 on um1.UserID = um2.AmmID        
left outer join OOOMaster om on er.FieldMarketingManager = om.ApproverID and om.OOOflag=1