MSSQL: Is this possible...?

GravyMonster · 9 Jun 2009 at 16:08

Hi All,

I've got a SQL statement which exports a mailing list which is generated via classic ASP. I'd like to switch this to a stored procedure for obvious reasons.

The SQL statement is...

Code:

SELECT EmailAddress, OptInConfirmedDate, UnsubscribedDate, 
	-- \/ \/ This bit is generated by an ASP loop
	(SELECT TOP 1 CustomValue FROM MailingList_Import_Values WHERE CustomFieldID = 1 AND MailingListMemberID = MailingList_Members.MailingListMemberID ORDER BY DateAdded DESC) AS [Firstname],
	(SELECT TOP 1 CustomValue FROM MailingList_Import_Values WHERE CustomFieldID = 2 AND MailingListMemberID = MailingList_Members.MailingListMemberID ORDER BY DateAdded DESC) AS [Surname]	 
	-- /\ /\ This bit is generated by an ASP loop
FROM MailingList_Members WHERE MailingListID = 1AND ((OptInConfirmed = 1) OR (IsUnsubscribed = 1)) ORDER BY EmailAddress

The bit in the loop is generated by running a separate query in ASP which finds all custom fields for the mailing list.

Would it be possible to replicate this loop in SQL?

I've tried to keep this as simple as possible so I've not posted the table structure, let me know if it'd make more sense if i posted that too.

Pho · 9 Jun 2009 at 16:26

Post the table structure of everything involved. I'll have a look when I get home. Now to go back to writing my own stored procedures

.

GravyMonster · 9 Jun 2009 at 16:29

Cheers Pho.

Code:

MailingList_Members
MailingListMemberID	MailingListID	AccountID	EmailAddress	OptInConfirmed	IsUnsubscribed	UnsubscribedDate	DateAdded
---------------------------------------------------------------------------------------------------------------------------------------------
1			5		1		[email protected]		True		False		NULL			01/01/09
2			5		1		[email protected]		True		False		NULL			01/02/09
3			7		2		[email protected]		True		False		NULL			02/01/09

CustomFields
CustomFieldID	AccountID	FieldName
------------------------------------------------------
1		1		Firstname
2		1		Surname
3		2		CompanyName

MailingList_Import_Values
ValueID		CustomFieldID	MailingListMemberID	CustomValue
-----------------------------------------------------------------------------
1		1		1			James
2		2		1			Cameron
3		1		2			Steven
4		2		2			Spielberg
5		3		3			Microsoft

So, if I ran the query under AccountID #1, I'd get..

Code:

SELECT EmailAddress, OptInConfirmedDate, UnsubscribedDate, 
	-- \/ \/ This bit is generated by an ASP loop
	(SELECT TOP 1 CustomValue FROM MailingList_Import_Values WHERE CustomFieldID = 1 AND MailingListMemberID = MailingList_Members.MailingListMemberID ORDER BY DateAdded DESC) AS [Firstname],
	(SELECT TOP 1 CustomValue FROM MailingList_Import_Values WHERE CustomFieldID = 2 AND MailingListMemberID = MailingList_Members.MailingListMemberID ORDER BY DateAdded DESC) AS [Surname]	 
	-- /\ /\ This bit is generated by an ASP loop
FROM MailingList_Members WHERE MailingListID = 2805 AND ((OptInConfirmed = 1) OR (IsUnsubscribed = 1)) ORDER BY EmailAddress

And Account #2 would get...

Code:

SELECT EmailAddress, OptInConfirmedDate, UnsubscribedDate, 
	-- \/ \/ This bit is generated by an ASP loop
	(SELECT TOP 1 CustomValue FROM MailingList_Import_Values WHERE CustomFieldID = 3 AND MailingListMemberID = MailingList_Members.MailingListMemberID ORDER BY DateAdded DESC) AS [CompanyName]
	-- /\ /\ This bit is generated by an ASP loop
FROM MailingList_Members WHERE MailingListID = 2805 AND ((OptInConfirmed = 1) OR (IsUnsubscribed = 1)) ORDER BY EmailAddress

Pho · 9 Jun 2009 at 20:29

Finally made it

.

Something like this should work:

PHP:

SELECT

	 EmailAddress
	,OptInConfirmedDate
	,UnsubscribedDate
	,miv_forename.CustomValue  as Firstname
	,miv_surname.CustomValue   as Surname
	
FROM
	MailingList_Members members
	
INNER JOIN
	MailingList_Import_Values miv_forename
	ON miv_forename.MailingListMemberID = members.AccountID AND miv_forename.CustomFieldID = 1
	
INNER JOIN
	MailingList_Import_Values miv_surname
	ON miv_surname.MailingListMemberID = members.AccountID AND miv_surname.CustomFieldID = 2	

WHERE

	MailingListID = 1
	AND
	(
			OptInConfirmed = 1
			OR
			IsUnsubscribed = 1
	)
		
ORDER BY EmailAddress

There's probably a better way instead of joining to the same table twice though.