Dennis’s Weblog

SQL language

Posted on: July 25, 2009

use ccmcms
select t4.id, t4.memberid, t5.memberid, t5.createdatetime
from member as t4 inner join memberdetail as t5 on t4.id = t5.memberid
where t5.createdatetime in
(
select max(t3.createdatetime) from
(
select t1.id as t3id, t1.memberid as t1memberid, t2.memberid as t2memberid, t2.createdatetime
from member as t1
inner join MemberDetail as t2
on t1.id = t2.memberid
) as t3
group by t3id
)one member  have several memberdetail record. and we want to select all member information assiociate with the lastest memberdetail record.

one member  have several memberdetail record. and we want to select all member information assiociate with the lastest memberdetail record.

use ccmcms

select t4.id, t4.memberid, t5.memberid, t5.createdatetime

from member as t4 inner join memberdetail as t5 on t4.id = t5.memberid

where t5.createdatetime in

(

select max(t3.createdatetime) from

(

select t1.id as t3id, t1.memberid as t1memberid, t2.memberid as t2memberid, t2.createdatetime

from member as t1

inner join MemberDetail as t2

on t1.id = t2.memberid

) as t3

group by t3id

)

when you use group by, you should know that you must need some kind of max,sum…. function.

You use join to join data and filter the unnecessary data.

use ccmcms

select t9.id, t9.memberid, t9.t5memberid, t9.createdatetime from

(

select t4.id as id, t4.memberid, t5.memberid as t5memberid, t5.createdatetime

from member as t4 inner join memberdetail as t5 on t4.id = t5.memberid

) as t9 inner join

(

select t3.t3id, max(t3.createdatetime) as maxdatetime from

(

select t1.id as t3id, t1.memberid as t1memberid, t2.memberid as t2memberid, t2.createdatetime

from member as t1

inner join MemberDetail as t2

on t1.id = t2.memberid

) as t3

group by t3id

) as t10

on t9.id = t10.t3id and t9.createdatetime = t10.maxdatetime

Generate by system:

SELECT TOP (1)

[Project4].[C1] AS [C1],

[Project4].[Id] AS [Id],

[Project4].[EventCode] AS [EventCode],

[Project4].[Status] AS [Status],

[Project4].[Presenter] AS [Presenter],

[Project4].[SurveyFormId] AS [SurveyFormId],

[Project4].[NameCN] AS [NameCN],

[Project4].[NameEN] AS [NameEN],

[Project4].[NamePT] AS [NamePT],

[Project4].[NameZH] AS [NameZH],

[Project4].[CaseOfficier1] AS [CaseOfficier1],

[Project4].[CaseOfficier2] AS [CaseOfficier2],

[Project4].[AttachEventId] AS [AttachEventId],

[Project4].[PriceTypeA1] AS [PriceTypeA1],

[Project4].[PriceTypeA2] AS [PriceTypeA2],

[Project4].[PriceTypeA3] AS [PriceTypeA3],

[Project4].[PriceTypeA4] AS [PriceTypeA4],

[Project4].[PriceTypeA5] AS [PriceTypeA5],

[Project4].[PriceTypeAFree] AS [PriceTypeAFree],

[Project4].[PriceTypeB1] AS [PriceTypeB1],

[Project4].[PriceTypeB2] AS [PriceTypeB2],

[Project4].[PriceTypeB3] AS [PriceTypeB3],

[Project4].[PriceTypeB4] AS [PriceTypeB4],

[Project4].[PriceTypeB5] AS [PriceTypeB5],

[Project4].[PriceTypeBFree] AS [PriceTypeBFree],

[Project4].[Organizer1CN] AS [Organizer1CN],

[Project4].[Organizer1EN] AS [Organizer1EN],

[Project4].[Organizer1PT] AS [Organizer1PT],

[Project4].[Organizer1ZH] AS [Organizer1ZH],

[Project4].[Organizer2CN] AS [Organizer2CN],

[Project4].[Organizer2EN] AS [Organizer2EN],

[Project4].[Organizer2PT] AS [Organizer2PT],

[Project4].[Organizer2ZH] AS [Organizer2ZH],

[Project4].[CoOrganizer1CN] AS [CoOrganizer1CN],

[Project4].[CoOrganizer1EN] AS [CoOrganizer1EN],

[Project4].[CoOrganizer1PT] AS [CoOrganizer1PT],

[Project4].[CoOrganizer1ZH] AS [CoOrganizer1ZH],

[Project4].[CoOrganizer2CN] AS [CoOrganizer2CN],

[Project4].[CoOrganizer2EN] AS [CoOrganizer2EN],

[Project4].[CoOrganizer2PT] AS [CoOrganizer2PT],

[Project4].[CoOrganizer2ZH] AS [CoOrganizer2ZH],

[Project4].[ProductionCN] AS [ProductionCN],

[Project4].[ProductionEN] AS [ProductionEN],

[Project4].[ProductionPT] AS [ProductionPT],

[Project4].[ProductionZH] AS [ProductionZH],

[Project4].[PartnerCN] AS [PartnerCN],

[Project4].[PartnerEN] AS [PartnerEN],

[Project4].[PartnerPT] AS [PartnerPT],

[Project4].[PartnerZH] AS [PartnerZH],

[Project4].[SponsorCN] AS [SponsorCN],

[Project4].[SponsorEN] AS [SponsorEN],

[Project4].[SponsorPT] AS [SponsorPT],

[Project4].[SponsorZH] AS [SponsorZH],

[Project4].[PerfromingGroup1CN] AS [PerfromingGroup1CN],

[Project4].[PerfromingGroup1EN] AS [PerfromingGroup1EN],

[Project4].[PerfromingGroup1PT] AS [PerfromingGroup1PT],

[Project4].[PerfromingGroup1ZH] AS [PerfromingGroup1ZH],

[Project4].[PerfromingGroup2CN] AS [PerfromingGroup2CN],

[Project4].[PerfromingGroup2EN] AS [PerfromingGroup2EN],

[Project4].[PerfromingGroup2PT] AS [PerfromingGroup2PT],

[Project4].[PerfromingGroup2ZH] AS [PerfromingGroup2ZH],

[Project4].[Language] AS [Language],

[Project4].[LanguageOtherCN] AS [LanguageOtherCN],

[Project4].[LanguageOtherEN] AS [LanguageOtherEN],

[Project4].[LanguageOtherPT] AS [LanguageOtherPT],

[Project4].[LanguageOtherZH] AS [LanguageOtherZH],

[Project4].[Subtitle] AS [Subtitle],

[Project4].[SubtitleOtherCN] AS [SubtitleOtherCN],

[Project4].[SubtitleOtherEN] AS [SubtitleOtherEN],

[Project4].[SubtitleOtherPT] AS [SubtitleOtherPT],

[Project4].[SubtitleOtherZH] AS [SubtitleOtherZH],

[Project4].[Duration] AS [Duration],

[Project4].[Interval] AS [Interval],

[Project4].[IntervalDuration] AS [IntervalDuration],

[Project4].[AgeLimit] AS [AgeLimit],

[Project4].[BoxOfficeOpenFrom] AS [BoxOfficeOpenFrom],

[Project4].[BoxOfficeOpenTo] AS [BoxOfficeOpenTo],

[Project4].[EarlyBirdFrom] AS [EarlyBirdFrom],

[Project4].[EarlyBirdTo] AS [EarlyBirdTo],

[Project4].[Coupon] AS [Coupon],

[Project4].[CouponCN] AS [CouponCN],

[Project4].[CouponEN] AS [CouponEN],

[Project4].[CouponPT] AS [CouponPT],

[Project4].[CouponZH] AS [CouponZH],

[Project4].[Hotline] AS [Hotline],

[Project4].[Pic1Path] AS [Pic1Path],

[Project4].[Pic2Path] AS [Pic2Path],

[Project4].[Pic3Path] AS [Pic3Path],

[Project4].[FilmOriginCN] AS [FilmOriginCN],

[Project4].[FilmOriginEN] AS [FilmOriginEN],

[Project4].[FilmOriginPT] AS [FilmOriginPT],

[Project4].[FilmOriginZH] AS [FilmOriginZH],

[Project4].[FilmYear] AS [FilmYear],

[Project4].[FilmColor] AS [FilmColor],

[Project4].[FilmColorOther] AS [FilmColorOther],

[Project4].[FilmFormat] AS [FilmFormat],

[Project4].[FilmFormatOther] AS [FilmFormatOther],

[Project4].[FilmRatio] AS [FilmRatio],

[Project4].[FilmRatioOther] AS [FilmRatioOther],

[Project4].[FilmRating] AS [FilmRating],

[Project4].[Highlight] AS [Highlight],

[Project4].[Highlight2] AS [Highlight2],

[Project4].[SeatPlan] AS [SeatPlan],

[Project4].[TicketURL] AS [TicketURL],

[Project4].[TicketStatusCN] AS [TicketStatusCN],

[Project4].[TicketStatusZH] AS [TicketStatusZH],

[Project4].[TicketStatusEN] AS [TicketStatusEN],

[Project4].[TicketStatusPT] AS [TicketStatusPT],

[Project4].[TermsConditionOthCN] AS [TermsConditionOthCN],

[Project4].[TermsConditionOthZH] AS [TermsConditionOthZH],

[Project4].[TermsConditionOthPT] AS [TermsConditionOthPT],

[Project4].[TermsConditionOthEN] AS [TermsConditionOthEN],

[Project4].[DiscountOthCN] AS [DiscountOthCN],

[Project4].[DiscountOthZH] AS [DiscountOthZH],

[Project4].[DiscountOthPT] AS [DiscountOthPT],

[Project4].[DiscountOthEN] AS [DiscountOthEN],

[Project4].[Course] AS [Course],

[Project4].[ArtsActivities] AS [ArtsActivities],

[Project4].[GroupId] AS [GroupId],

[Project4].[FacilityId] AS [FacilityId]

FROM ( SELECT

[Project3].[Id] AS [Id],

[Project3].[EventCode] AS [EventCode],

[Project3].[Status] AS [Status],

[Project3].[Presenter] AS [Presenter],

[Project3].[SurveyFormId] AS [SurveyFormId],

[Project3].[NameCN] AS [NameCN],

[Project3].[NameEN] AS [NameEN],

[Project3].[NamePT] AS [NamePT],

[Project3].[NameZH] AS [NameZH],

[Project3].[CaseOfficier1] AS [CaseOfficier1],

[Project3].[CaseOfficier2] AS [CaseOfficier2],

[Project3].[GroupId] AS [GroupId],

[Project3].[AttachEventId] AS [AttachEventId],

[Project3].[FacilityId] AS [FacilityId],

[Project3].[PriceTypeA1] AS [PriceTypeA1],

[Project3].[PriceTypeA2] AS [PriceTypeA2],

[Project3].[PriceTypeA3] AS [PriceTypeA3],

[Project3].[PriceTypeA4] AS [PriceTypeA4],

[Project3].[PriceTypeA5] AS [PriceTypeA5],

[Project3].[PriceTypeAFree] AS [PriceTypeAFree],

[Project3].[PriceTypeB1] AS [PriceTypeB1],

[Project3].[PriceTypeB2] AS [PriceTypeB2],

[Project3].[PriceTypeB3] AS [PriceTypeB3],

[Project3].[PriceTypeB4] AS [PriceTypeB4],

[Project3].[PriceTypeB5] AS [PriceTypeB5],

[Project3].[PriceTypeBFree] AS [PriceTypeBFree],

[Project3].[Organizer1CN] AS [Organizer1CN],

[Project3].[Organizer1EN] AS [Organizer1EN],

[Project3].[Organizer1PT] AS [Organizer1PT],

[Project3].[Organizer1ZH] AS [Organizer1ZH],

[Project3].[Organizer2CN] AS [Organizer2CN],

[Project3].[Organizer2EN] AS [Organizer2EN],

[Project3].[Organizer2PT] AS [Organizer2PT],

[Project3].[Organizer2ZH] AS [Organizer2ZH],

[Project3].[CoOrganizer1CN] AS [CoOrganizer1CN],

[Project3].[CoOrganizer1EN] AS [CoOrganizer1EN],

[Project3].[CoOrganizer1PT] AS [CoOrganizer1PT],

[Project3].[CoOrganizer1ZH] AS [CoOrganizer1ZH],

[Project3].[CoOrganizer2CN] AS [CoOrganizer2CN],

[Project3].[CoOrganizer2EN] AS [CoOrganizer2EN],

[Project3].[CoOrganizer2PT] AS [CoOrganizer2PT],

[Project3].[CoOrganizer2ZH] AS [CoOrganizer2ZH],

[Project3].[ProductionCN] AS [ProductionCN],

[Project3].[ProductionEN] AS [ProductionEN],

[Project3].[ProductionPT] AS [ProductionPT],

[Project3].[ProductionZH] AS [ProductionZH],

[Project3].[PartnerCN] AS [PartnerCN],

[Project3].[PartnerEN] AS [PartnerEN],

[Project3].[PartnerPT] AS [PartnerPT],

[Project3].[PartnerZH] AS [PartnerZH],

[Project3].[SponsorCN] AS [SponsorCN],

[Project3].[SponsorEN] AS [SponsorEN],

[Project3].[SponsorPT] AS [SponsorPT],

[Project3].[SponsorZH] AS [SponsorZH],

[Project3].[PerfromingGroup1CN] AS [PerfromingGroup1CN],

[Project3].[PerfromingGroup1EN] AS [PerfromingGroup1EN],

[Project3].[PerfromingGroup1PT] AS [PerfromingGroup1PT],

[Project3].[PerfromingGroup1ZH] AS [PerfromingGroup1ZH],

[Project3].[PerfromingGroup2CN] AS [PerfromingGroup2CN],

[Project3].[PerfromingGroup2EN] AS [PerfromingGroup2EN],

[Project3].[PerfromingGroup2PT] AS [PerfromingGroup2PT],

[Project3].[PerfromingGroup2ZH] AS [PerfromingGroup2ZH],

[Project3].[Language] AS [Language],

[Project3].[LanguageOtherCN] AS [LanguageOtherCN],

[Project3].[LanguageOtherEN] AS [LanguageOtherEN],

[Project3].[LanguageOtherPT] AS [LanguageOtherPT],

[Project3].[LanguageOtherZH] AS [LanguageOtherZH],

[Project3].[Subtitle] AS [Subtitle],

[Project3].[SubtitleOtherCN] AS [SubtitleOtherCN],

[Project3].[SubtitleOtherEN] AS [SubtitleOtherEN],

[Project3].[SubtitleOtherPT] AS [SubtitleOtherPT],

[Project3].[SubtitleOtherZH] AS [SubtitleOtherZH],

[Project3].[Duration] AS [Duration],

[Project3].[Interval] AS [Interval],

[Project3].[IntervalDuration] AS [IntervalDuration],

[Project3].[AgeLimit] AS [AgeLimit],

[Project3].[BoxOfficeOpenFrom] AS [BoxOfficeOpenFrom],

[Project3].[BoxOfficeOpenTo] AS [BoxOfficeOpenTo],

[Project3].[EarlyBirdFrom] AS [EarlyBirdFrom],

[Project3].[EarlyBirdTo] AS [EarlyBirdTo],

[Project3].[Coupon] AS [Coupon],

[Project3].[CouponCN] AS [CouponCN],

[Project3].[CouponEN] AS [CouponEN],

[Project3].[CouponPT] AS [CouponPT],

[Project3].[CouponZH] AS [CouponZH],

[Project3].[Hotline] AS [Hotline],

[Project3].[Pic1Path] AS [Pic1Path],

[Project3].[Pic2Path] AS [Pic2Path],

[Project3].[Pic3Path] AS [Pic3Path],

[Project3].[FilmOriginCN] AS [FilmOriginCN],

[Project3].[FilmOriginEN] AS [FilmOriginEN],

[Project3].[FilmOriginPT] AS [FilmOriginPT],

[Project3].[FilmOriginZH] AS [FilmOriginZH],

[Project3].[FilmYear] AS [FilmYear],

[Project3].[FilmColor] AS [FilmColor],

[Project3].[FilmColorOther] AS [FilmColorOther],

[Project3].[FilmFormat] AS [FilmFormat],

[Project3].[FilmFormatOther] AS [FilmFormatOther],

[Project3].[FilmRatio] AS [FilmRatio],

[Project3].[FilmRatioOther] AS [FilmRatioOther],

[Project3].[FilmRating] AS [FilmRating],

[Project3].[Highlight] AS [Highlight],

[Project3].[Highlight2] AS [Highlight2],

[Project3].[SeatPlan] AS [SeatPlan],

[Project3].[TicketURL] AS [TicketURL],

[Project3].[TicketStatusCN] AS [TicketStatusCN],

[Project3].[TicketStatusZH] AS [TicketStatusZH],

[Project3].[TicketStatusEN] AS [TicketStatusEN],

[Project3].[TicketStatusPT] AS [TicketStatusPT],

[Project3].[TermsConditionOthCN] AS [TermsConditionOthCN],

[Project3].[TermsConditionOthZH] AS [TermsConditionOthZH],

[Project3].[TermsConditionOthPT] AS [TermsConditionOthPT],

[Project3].[TermsConditionOthEN] AS [TermsConditionOthEN],

[Project3].[DiscountOthCN] AS [DiscountOthCN],

[Project3].[DiscountOthZH] AS [DiscountOthZH],

[Project3].[DiscountOthPT] AS [DiscountOthPT],

[Project3].[DiscountOthEN] AS [DiscountOthEN],

[Project3].[Course] AS [Course],

[Project3].[ArtsActivities] AS [ArtsActivities],

1 AS [C1],

[Project3].[C1] AS [C2]

FROM ( SELECT

[Extent1].[Id] AS [Id],

[Extent1].[EventCode] AS [EventCode],

[Extent1].[Status] AS [Status],

[Extent1].[Presenter] AS [Presenter],

[Extent1].[SurveyFormId] AS [SurveyFormId],

[Extent1].[NameCN] AS [NameCN],

[Extent1].[NameEN] AS [NameEN],

[Extent1].[NamePT] AS [NamePT],

[Extent1].[NameZH] AS [NameZH],

[Extent1].[CaseOfficier1] AS [CaseOfficier1],

[Extent1].[CaseOfficier2] AS [CaseOfficier2],

[Extent1].[GroupId] AS [GroupId],

[Extent1].[AttachEventId] AS [AttachEventId],

[Extent1].[FacilityId] AS [FacilityId],

[Extent1].[PriceTypeA1] AS [PriceTypeA1],

[Extent1].[PriceTypeA2] AS [PriceTypeA2],

[Extent1].[PriceTypeA3] AS [PriceTypeA3],

[Extent1].[PriceTypeA4] AS [PriceTypeA4],

[Extent1].[PriceTypeA5] AS [PriceTypeA5],

[Extent1].[PriceTypeAFree] AS [PriceTypeAFree],

[Extent1].[PriceTypeB1] AS [PriceTypeB1],

[Extent1].[PriceTypeB2] AS [PriceTypeB2],

[Extent1].[PriceTypeB3] AS [PriceTypeB3],

[Extent1].[PriceTypeB4] AS [PriceTypeB4],

[Extent1].[PriceTypeB5] AS [PriceTypeB5],

[Extent1].[PriceTypeBFree] AS [PriceTypeBFree],

[Extent1].[Organizer1CN] AS [Organizer1CN],

[Extent1].[Organizer1EN] AS [Organizer1EN],

[Extent1].[Organizer1PT] AS [Organizer1PT],

[Extent1].[Organizer1ZH] AS [Organizer1ZH],

[Extent1].[Organizer2CN] AS [Organizer2CN],

[Extent1].[Organizer2EN] AS [Organizer2EN],

[Extent1].[Organizer2PT] AS [Organizer2PT],

[Extent1].[Organizer2ZH] AS [Organizer2ZH],

[Extent1].[CoOrganizer1CN] AS [CoOrganizer1CN],

[Extent1].[CoOrganizer1EN] AS [CoOrganizer1EN],

[Extent1].[CoOrganizer1PT] AS [CoOrganizer1PT],

[Extent1].[CoOrganizer1ZH] AS [CoOrganizer1ZH],

[Extent1].[CoOrganizer2CN] AS [CoOrganizer2CN],

[Extent1].[CoOrganizer2EN] AS [CoOrganizer2EN],

[Extent1].[CoOrganizer2PT] AS [CoOrganizer2PT],

[Extent1].[CoOrganizer2ZH] AS [CoOrganizer2ZH],

[Extent1].[ProductionCN] AS [ProductionCN],

[Extent1].[ProductionEN] AS [ProductionEN],

[Extent1].[ProductionPT] AS [ProductionPT],

[Extent1].[ProductionZH] AS [ProductionZH],

[Extent1].[PartnerCN] AS [PartnerCN],

[Extent1].[PartnerEN] AS [PartnerEN],

[Extent1].[PartnerPT] AS [PartnerPT],

[Extent1].[PartnerZH] AS [PartnerZH],

[Extent1].[SponsorCN] AS [SponsorCN],

[Extent1].[SponsorEN] AS [SponsorEN],

[Extent1].[SponsorPT] AS [SponsorPT],

[Extent1].[SponsorZH] AS [SponsorZH],

[Extent1].[PerfromingGroup1CN] AS [PerfromingGroup1CN],

[Extent1].[PerfromingGroup1EN] AS [PerfromingGroup1EN],

[Extent1].[PerfromingGroup1PT] AS [PerfromingGroup1PT],

[Extent1].[PerfromingGroup1ZH] AS [PerfromingGroup1ZH],

[Extent1].[PerfromingGroup2CN] AS [PerfromingGroup2CN],

[Extent1].[PerfromingGroup2EN] AS [PerfromingGroup2EN],

[Extent1].[PerfromingGroup2PT] AS [PerfromingGroup2PT],

[Extent1].[PerfromingGroup2ZH] AS [PerfromingGroup2ZH],

[Extent1].[Language] AS [Language],

[Extent1].[LanguageOtherCN] AS [LanguageOtherCN],

[Extent1].[LanguageOtherEN] AS [LanguageOtherEN],

[Extent1].[LanguageOtherPT] AS [LanguageOtherPT],

[Extent1].[LanguageOtherZH] AS [LanguageOtherZH],

[Extent1].[Subtitle] AS [Subtitle],

[Extent1].[SubtitleOtherCN] AS [SubtitleOtherCN],

[Extent1].[SubtitleOtherEN] AS [SubtitleOtherEN],

[Extent1].[SubtitleOtherPT] AS [SubtitleOtherPT],

[Extent1].[SubtitleOtherZH] AS [SubtitleOtherZH],

[Extent1].[Duration] AS [Duration],

[Extent1].[Interval] AS [Interval],

[Extent1].[IntervalDuration] AS [IntervalDuration],

[Extent1].[AgeLimit] AS [AgeLimit],

[Extent1].[BoxOfficeOpenFrom] AS [BoxOfficeOpenFrom],

[Extent1].[BoxOfficeOpenTo] AS [BoxOfficeOpenTo],

[Extent1].[EarlyBirdFrom] AS [EarlyBirdFrom],

[Extent1].[EarlyBirdTo] AS [EarlyBirdTo],

[Extent1].[Coupon] AS [Coupon],

[Extent1].[CouponCN] AS [CouponCN],

[Extent1].[CouponEN] AS [CouponEN],

[Extent1].[CouponPT] AS [CouponPT],

[Extent1].[CouponZH] AS [CouponZH],

[Extent1].[Hotline] AS [Hotline],

[Extent1].[Pic1Path] AS [Pic1Path],

[Extent1].[Pic2Path] AS [Pic2Path],

[Extent1].[Pic3Path] AS [Pic3Path],

[Extent1].[FilmOriginCN] AS [FilmOriginCN],

[Extent1].[FilmOriginEN] AS [FilmOriginEN],

[Extent1].[FilmOriginPT] AS [FilmOriginPT],

[Extent1].[FilmOriginZH] AS [FilmOriginZH],

[Extent1].[FilmYear] AS [FilmYear],

[Extent1].[FilmColor] AS [FilmColor],

[Extent1].[FilmColorOther] AS [FilmColorOther],

[Extent1].[FilmFormat] AS [FilmFormat],

[Extent1].[FilmFormatOther] AS [FilmFormatOther],

[Extent1].[FilmRatio] AS [FilmRatio],

[Extent1].[FilmRatioOther] AS [FilmRatioOther],

[Extent1].[FilmRating] AS [FilmRating],

[Extent1].[Highlight] AS [Highlight],

[Extent1].[Highlight2] AS [Highlight2],

[Extent1].[SeatPlan] AS [SeatPlan],

[Extent1].[TicketURL] AS [TicketURL],

[Extent1].[TicketStatusCN] AS [TicketStatusCN],

[Extent1].[TicketStatusZH] AS [TicketStatusZH],

[Extent1].[TicketStatusEN] AS [TicketStatusEN],

[Extent1].[TicketStatusPT] AS [TicketStatusPT],

[Extent1].[TermsConditionOthCN] AS [TermsConditionOthCN],

[Extent1].[TermsConditionOthZH] AS [TermsConditionOthZH],

[Extent1].[TermsConditionOthPT] AS [TermsConditionOthPT],

[Extent1].[TermsConditionOthEN] AS [TermsConditionOthEN],

[Extent1].[DiscountOthCN] AS [DiscountOthCN],

[Extent1].[DiscountOthZH] AS [DiscountOthZH],

[Extent1].[DiscountOthPT] AS [DiscountOthPT],

[Extent1].[DiscountOthEN] AS [DiscountOthEN],

[Extent1].[Course] AS [Course],

[Extent1].[ArtsActivities] AS [ArtsActivities],

(SELECT TOP (1) [Project2].[EventDatetime] AS [EventDatetime]

FROM ( SELECT

[Extent3].[EventDatetime] AS [EventDatetime]

FROM [dbo].[WebEventPrice] AS [Extent3]

WHERE ([Extent1].[Id] = [Extent3].[EventId]) AND ([Extent3].[EventDatetime] >= convert(datetime, ‘2009-07-01 00:00:00.000’, 121))

)  AS [Project2]

ORDER BY [Project2].[EventDatetime] ASC) AS [C1]

FROM [dbo].[WebEvent] AS [Extent1]

WHERE (1 = [Extent1].[Status]) AND (([Extent1].[ArtsActivities] IS NULL) OR (0 = [Extent1].[ArtsActivities])) AND ( EXISTS (SELECT

cast(1 as bit) AS [C1]

FROM [dbo].[WebEventPrice] AS [Extent2]

WHERE ([Extent1].[Id] = [Extent2].[EventId]) AND (convert(datetime, ‘2009-07-01 00:00:00.000’, 121) <= [Extent2].[EventDatetime])

))

)  AS [Project3]

)  AS [Project4]

ORDER BY [Project4].[C2] ASC

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: