Help with Aggregate Query

[Patrick Jackman]

I have a set of ContactIDs, EmailAddresses and EmailAddressTypes (1=Bus,
2=Bus&Pers, 3=Pers).

I need to return a list of 1 email address per Contact and where a Contact
has more than 1 email address, I need to pick the first one sorted by
EmailAddressType. That is, if a Contact has both a type2 and a type3 email
address, then return the type2.

I can do it with this crosstab subquery, but is there a "nicer" way?

SELECT EM.OrgContactID, IIf(Not IsNull([EM].[1]),[EM].[1],IIf(Not
IsNull([EM].[2]),[EM].[2],[EM].[3])) AS EMail
FROM [TRANSFORM First(tblPartyECom.EAddress) AS FirstOfEAddress SELECT
tblPartyCM.OrgContactID FROM tblPartyCM INNER JOIN (tblCMUsageType INNER
JOIN tblPartyECom ON tblCMUsageType.CMUsageTypeID =
tblPartyECom.CMUsageTypeID) ON tblPartyCM.PartyCMID =
tblPartyECom.PartyEComID WHERE (((tblPartyECom.CMCategoryTypeID)=5) AND
((tblPartyECom.ThruDate) Is Null)) GROUP BY tblPartyCM.OrgContactID ORDER BY
tblCMUsageType.CMUsageSort PIVOT tblCMUsageType.CMUsageSort In (1,2,3)]. AS
EM;


Thanks.

Patrick
=-=-=-=-=-=-=-=-=-=-=-=-=-
Patrick Jackman
Vancouver, BC

 

[Allen Browne]

If one client can have many email address of different types, you need to
store this in 3 tables:

a) tblEmailType (one record for each type, with EmailTypeID as primary key,
where lower numbers mean higher priority.)

b) tblClient (one record for each client, with ClientID as primary key but
no email fields);

c) tblClientEmail, with fields like this:
- ClientEmailID primary key
- ClientID relates to tblClient.ClientID
- Email text

You can then get the preferred email address like this:
SELECT tblClient.ClientID,
(SELECT TOP 1 Email
FROM tblClientEmail
WHERE tblClientEmail.ClientID = tblClient.ClientID
AND tblClientEmail.Email Is Not Null
ORDER BY tblClientEmail.EmailTypeID, tblClientEmail.ClientEmailID)
AS Email
FROM tblClient;

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

 

[Patrick Jackman]

Thanks very much Allen. My schema is a bit more complex but your schema is
very helpful along with your explanation.

Unfortunately, your SQL approach takes much longer to run (30 times maybe)
than the crosstab version that I posted below.

My schema:
tblPartyCM [holds PartyContactMechanisms; PostalAddress, TelecomNumber,
InternetAddress]
PartyCMID
CMTypeID
PartyID (Organization or Person in the database)
OrgContactID (Person who works at an Organization)

tblPartyECom [holds email, website, blog, IM addresses]
PartyEComID (1 to 1 relationship with tblParty.PartyCMID
EAddress
CMCategoryTypeID (5 is Email Address)
CMUseageTypeID
FromDate
ThruDate

tblCMUseageType [holds useage (1=Business, 2=Personal, 3=Business&Personal,
4=Emergency)]
CMUseageTypeID
CMUseageTypeName
CMUseageSort

Patrick.

If one client can have many email address of different types, you need to
store this in 3 tables:

a) tblEmailType (one record for each type, with EmailTypeID as primary
key, where lower numbers mean higher priority.)

b) tblClient (one record for each client, with ClientID as primary key but
no email fields);

c) tblClientEmail, with fields like this:
- ClientEmailID primary key
- ClientID relates to tblClient.ClientID
- Email text

You can then get the preferred email address like this:
SELECT tblClient.ClientID,
(SELECT TOP 1 Email
FROM tblClientEmail
WHERE tblClientEmail.ClientID = tblClient.ClientID
AND tblClientEmail.Email Is Not Null
ORDER BY tblClientEmail.EmailTypeID, tblClientEmail.ClientEmailID)
AS Email
FROM tblClient;

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a set of ContactIDs, EmailAddresses and EmailAddressTypes (1=Bus,
2=Bus&Pers, 3=Pers).

I need to return a list of 1 email address per Contact and where a
Contact
has more than 1 email address, I need to pick the first one sorted by
EmailAddressType. That is, if a Contact has both a type2 and a type3
email
address, then return the type2.

I can do it with this crosstab subquery, but is there a "nicer" way?

SELECT EM.OrgContactID, IIf(Not IsNull([EM].[1]),[EM].[1],IIf(Not
IsNull([EM].[2]),[EM].[2],[EM].[3])) AS EMail
FROM [TRANSFORM First(tblPartyECom.EAddress) AS FirstOfEAddress SELECT
tblPartyCM.OrgContactID FROM tblPartyCM INNER JOIN (tblCMUsageType INNER
JOIN tblPartyECom ON tblCMUsageType.CMUsageTypeID =
tblPartyECom.CMUsageTypeID) ON tblPartyCM.PartyCMID =
tblPartyECom.PartyEComID WHERE (((tblPartyECom.CMCategoryTypeID)=5) AND
((tblPartyECom.ThruDate) Is Null)) GROUP BY tblPartyCM.OrgContactID ORDER
BY tblCMUsageType.CMUsageSort PIVOT tblCMUsageType.CMUsageSort In
(1,2,3)]. AS EM;


Thanks.

Patrick
=-=-=-=-=-=-=-=-=-=-=-=-=-
Patrick Jackman
Vancouver, BC

 

[Patrick Jackman]

Hello again Allen. I posted a question about this to a SQL Server group and
Erland Sommarskog offered a reply that translates to Access as follows.
Erland's solution takes roughly 2 seconds. My crosstab takes about 1 second.
So in this case "ugly" is winning although I wouldn't want to expand it to
situations where there are more than 3 possible EmailTypes.

SELECT CM.OrgContactID, EC.EAddress
FROM tblCMUsageType AS UT
INNER JOIN
(tblPartyCM AS CM INNER JOIN tblPartyECom AS EC ON CM.PartyCMID =
EC.PartyEComID)
ON UT.CMUsageTypeID = EC.CMUsageTypeID
WHERE (EC.CMCategoryTypeID=5)
AND (EC.ThruDate Is Null)
AND (UT.CMUsageSort=
(SELECT Min([CMUsageSort]) AS MinSort FROM tblPartyCM AS CM2 INNER JOIN
(tblCMUsageType AS UT2 INNER JOIN tblPartyECom AS EC2 ON UT2.CMUsageTypeID =
EC2.CMUsageTypeID) ON CM2.PartyCMID = EC2.PartyEComID WHERE CM2.OrgContactID
= CM.OrgContactID))


SELECT EM.OrgContactID, IIf(Not IsNull([EM].[1]),[EM].[1],IIf(Not
IsNull([EM].[2]),[EM].[2],[EM].[3])) AS EMail
FROM
[TRANSFORM First(tblPartyECom.EAddress) AS FirstOfEAddress
SELECT tblPartyCM.OrgContactID
FROM tblPartyCM
INNER JOIN
(tblCMUsageType INNER JOIN tblPartyECom ON
tblCMUsageType.CMUsageTypeID
=tblPartyECom.CMUsageTypeID)
ON tblPartyCM.PartyCMID = tblPartyECom.PartyEComID
WHERE (tblPartyECom.CMCategoryTypeID=5)
AND (tblPartyECom.ThruDate Is Null)
GROUP BY tblPartyCM.OrgContactID ORDER BY tblCMUsageType.CMUsageSort
PIVOT tblCMUsageType.CMUsageSort In (1,2,3)].
AS EM;

A SQL Server 2008 sample follows (with "tbl" prefixes removed)

DROP TYPE PartyCM;

DROP TYPE PartyECom;

DROP TYPE CMUsageType;

GO

CREATE TYPE PartyCM AS TABLE (

PartyCMID INT NOT NULL,

CMTypeID SMALLINT NOT NULL,

PartyID INT NOT NULL,

OrgContactID INT)

GO

CREATE TYPE PartyECom AS TABLE (

PartyEComID INT NOT NULL,

EAddress VARCHAR(125) NOT NULL,

CMUsageTypeID SMALLINT NOT NULL,

FromDate DATE NOT NULL,

ThruDate Date)

GO

CREATE TYPE CMUsageType AS TABLE (

CMUsageTypeID SMALLINT NOT NULL,

CMUsageTypeName VARCHAR(20) NOT NULL,

CMUsageSort SMALLINT NOT NULL)

GO

DECLARE @UT CMUsageType;

DECLARE @CM PartyCM;

DECLARE @EC PartyECom;

INSERT @UT EXEC('

SELECT 1, ''Business'', 1

SELECT 2, ''Personal'', 3

SELECT 3, ''Bus & Pers'', 2

SELECT 4, ''Emergency'', 4

')

INSERT @CM EXEC('

SELECT 1, 5, 101, 301

SELECT 2, 5, 102, 302

SELECT 3, 5, 102, 302

SELECT 4, 5, 103, 303

SELECT 5, 5, 104, 304

')

INSERT @EC EXEC('

SELECT 1, ''(e-mail address removed)'', 1, ''8/1/2009'', NULL

SELECT 2, ''(e-mail address removed)'', 2, ''8/1/2009'', NULL

SELECT 3, ''(e-mail address removed)'', 1, ''8/1/2009'', NULL

SELECT 4, ''(e-mail address removed)'', 1, ''8/1/2009'', NULL

SELECT 5, ''(e-mail address removed)'', 1, ''1/1/09'', ''8/1/2009''

')

-- Simple output

SELECT OrgContactID, EAddress, CMUsageTypeName

FROM @EC EC

INNER JOIN @CM CM

ON CM.PartyCMID = EC.PartyEComID

INNER JOIN @UT UT

ON UT.CMUsageTypeID = EC.CMUsageTypeID

WHERE (CM.CMTypeID=5) AND (EC.ThruDate IS NULL);

/* 301 (e-mail address removed) Business

302 (e-mail address removed) Personal

302 (e-mail address removed) Business

303 (e-mail address removed) Business

*/


-- Desired output

/* 301 (e-mail address removed) Business

302 (e-mail address removed) Business

303 (e-mail address removed) Business

*/

If one client can have many email address of different types, you need to
store this in 3 tables:

a) tblEmailType (one record for each type, with EmailTypeID as primary
key, where lower numbers mean higher priority.)

b) tblClient (one record for each client, with ClientID as primary key but
no email fields);

c) tblClientEmail, with fields like this:
- ClientEmailID primary key
- ClientID relates to tblClient.ClientID
- Email text

You can then get the preferred email address like this:
SELECT tblClient.ClientID,
(SELECT TOP 1 Email
FROM tblClientEmail
WHERE tblClientEmail.ClientID = tblClient.ClientID
AND tblClientEmail.Email Is Not Null
ORDER BY tblClientEmail.EmailTypeID, tblClientEmail.ClientEmailID)
AS Email
FROM tblClient;

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a set of ContactIDs, EmailAddresses and EmailAddressTypes (1=Bus,
2=Bus&Pers, 3=Pers).

I need to return a list of 1 email address per Contact and where a
Contact
has more than 1 email address, I need to pick the first one sorted by
EmailAddressType. That is, if a Contact has both a type2 and a type3
email
address, then return the type2.

I can do it with this crosstab subquery, but is there a "nicer" way?

SELECT EM.OrgContactID, IIf(Not IsNull([EM].[1]),[EM].[1],IIf(Not
IsNull([EM].[2]),[EM].[2],[EM].[3])) AS EMail
FROM [TRANSFORM First(tblPartyECom.EAddress) AS FirstOfEAddress SELECT
tblPartyCM.OrgContactID FROM tblPartyCM INNER JOIN (tblCMUsageType INNER
JOIN tblPartyECom ON tblCMUsageType.CMUsageTypeID =
tblPartyECom.CMUsageTypeID) ON tblPartyCM.PartyCMID =
tblPartyECom.PartyEComID WHERE (((tblPartyECom.CMCategoryTypeID)=5) AND
((tblPartyECom.ThruDate) Is Null)) GROUP BY tblPartyCM.OrgContactID ORDER
BY tblCMUsageType.CMUsageSort PIVOT tblCMUsageType.CMUsageSort In
(1,2,3)]. AS EM;


Thanks.

Patrick
=-=-=-=-=-=-=-=-=-=-=-=-=-
Patrick Jackman
Vancouver, BC

 

[Allen Browne]

Agreed: Access does tend to be slow in executing subqueries.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello again Allen. I posted a question about this to a SQL Server group
and Erland Sommarskog offered a reply that translates to Access as
follows. Erland's solution takes roughly 2 seconds. My crosstab takes
about 1 second. So in this case "ugly" is winning although I wouldn't want
to expand it to situations where there are more than 3 possible
EmailTypes.

SELECT CM.OrgContactID, EC.EAddress
FROM tblCMUsageType AS UT
INNER JOIN
(tblPartyCM AS CM INNER JOIN tblPartyECom AS EC ON CM.PartyCMID =
EC.PartyEComID)
ON UT.CMUsageTypeID = EC.CMUsageTypeID
WHERE (EC.CMCategoryTypeID=5)
AND (EC.ThruDate Is Null)
AND (UT.CMUsageSort=
(SELECT Min([CMUsageSort]) AS MinSort FROM tblPartyCM AS CM2 INNER JOIN
(tblCMUsageType AS UT2 INNER JOIN tblPartyECom AS EC2 ON UT2.CMUsageTypeID
= EC2.CMUsageTypeID) ON CM2.PartyCMID = EC2.PartyEComID WHERE
CM2.OrgContactID = CM.OrgContactID))


SELECT EM.OrgContactID, IIf(Not IsNull([EM].[1]),[EM].[1],IIf(Not
IsNull([EM].[2]),[EM].[2],[EM].[3])) AS EMail
FROM
[TRANSFORM First(tblPartyECom.EAddress) AS FirstOfEAddress
SELECT tblPartyCM.OrgContactID
FROM tblPartyCM
INNER JOIN
(tblCMUsageType INNER JOIN tblPartyECom ON
tblCMUsageType.CMUsageTypeID
=tblPartyECom.CMUsageTypeID)
ON tblPartyCM.PartyCMID = tblPartyECom.PartyEComID
WHERE (tblPartyECom.CMCategoryTypeID=5)
AND (tblPartyECom.ThruDate Is Null)
GROUP BY tblPartyCM.OrgContactID ORDER BY tblCMUsageType.CMUsageSort
PIVOT tblCMUsageType.CMUsageSort In (1,2,3)].
AS EM;

A SQL Server 2008 sample follows (with "tbl" prefixes removed)

DROP TYPE PartyCM;

DROP TYPE PartyECom;

DROP TYPE CMUsageType;

GO

CREATE TYPE PartyCM AS TABLE (

PartyCMID INT NOT NULL,

CMTypeID SMALLINT NOT NULL,

PartyID INT NOT NULL,

OrgContactID INT)

GO

CREATE TYPE PartyECom AS TABLE (

PartyEComID INT NOT NULL,

EAddress VARCHAR(125) NOT NULL,

CMUsageTypeID SMALLINT NOT NULL,

FromDate DATE NOT NULL,

ThruDate Date)

GO

CREATE TYPE CMUsageType AS TABLE (

CMUsageTypeID SMALLINT NOT NULL,

CMUsageTypeName VARCHAR(20) NOT NULL,

CMUsageSort SMALLINT NOT NULL)

GO

DECLARE @UT CMUsageType;

DECLARE @CM PartyCM;

DECLARE @EC PartyECom;

INSERT @UT EXEC('

SELECT 1, ''Business'', 1

SELECT 2, ''Personal'', 3

SELECT 3, ''Bus & Pers'', 2

SELECT 4, ''Emergency'', 4

')

INSERT @CM EXEC('

SELECT 1, 5, 101, 301

SELECT 2, 5, 102, 302

SELECT 3, 5, 102, 302

SELECT 4, 5, 103, 303

SELECT 5, 5, 104, 304

')

INSERT @EC EXEC('

SELECT 1, ''(e-mail address removed)'', 1, ''8/1/2009'', NULL

SELECT 2, ''(e-mail address removed)'', 2, ''8/1/2009'', NULL

SELECT 3, ''(e-mail address removed)'', 1, ''8/1/2009'', NULL

SELECT 4, ''(e-mail address removed)'', 1, ''8/1/2009'', NULL

SELECT 5, ''(e-mail address removed)'', 1, ''1/1/09'', ''8/1/2009''

')

-- Simple output

SELECT OrgContactID, EAddress, CMUsageTypeName

FROM @EC EC

INNER JOIN @CM CM

ON CM.PartyCMID = EC.PartyEComID

INNER JOIN @UT UT

ON UT.CMUsageTypeID = EC.CMUsageTypeID

WHERE (CM.CMTypeID=5) AND (EC.ThruDate IS NULL);

/* 301 (e-mail address removed) Business

302 (e-mail address removed) Personal

302 (e-mail address removed) Business

303 (e-mail address removed) Business

*/


-- Desired output

/* 301 (e-mail address removed) Business

302 (e-mail address removed) Business

303 (e-mail address removed) Business

*/

If one client can have many email address of different types, you need to
store this in 3 tables:

a) tblEmailType (one record for each type, with EmailTypeID as primary
key, where lower numbers mean higher priority.)

b) tblClient (one record for each client, with ClientID as primary key
but no email fields);

c) tblClientEmail, with fields like this:
- ClientEmailID primary key
- ClientID relates to tblClient.ClientID
- Email text

You can then get the preferred email address like this:
SELECT tblClient.ClientID,
(SELECT TOP 1 Email
FROM tblClientEmail
WHERE tblClientEmail.ClientID = tblClient.ClientID
AND tblClientEmail.Email Is Not Null
ORDER BY tblClientEmail.EmailTypeID, tblClientEmail.ClientEmailID)
AS Email
FROM tblClient;

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a set of ContactIDs, EmailAddresses and EmailAddressTypes (1=Bus,
2=Bus&Pers, 3=Pers).

I need to return a list of 1 email address per Contact and where a
Contact
has more than 1 email address, I need to pick the first one sorted by
EmailAddressType. That is, if a Contact has both a type2 and a type3
email
address, then return the type2.

I can do it with this crosstab subquery, but is there a "nicer" way?

SELECT EM.OrgContactID, IIf(Not IsNull([EM].[1]),[EM].[1],IIf(Not
IsNull([EM].[2]),[EM].[2],[EM].[3])) AS EMail
FROM [TRANSFORM First(tblPartyECom.EAddress) AS FirstOfEAddress SELECT
tblPartyCM.OrgContactID FROM tblPartyCM INNER JOIN (tblCMUsageType INNER
JOIN tblPartyECom ON tblCMUsageType.CMUsageTypeID =
tblPartyECom.CMUsageTypeID) ON tblPartyCM.PartyCMID =
tblPartyECom.PartyEComID WHERE (((tblPartyECom.CMCategoryTypeID)=5) AND
((tblPartyECom.ThruDate) Is Null)) GROUP BY tblPartyCM.OrgContactID
ORDER BY tblCMUsageType.CMUsageSort PIVOT tblCMUsageType.CMUsageSort In
(1,2,3)]. AS EM;


Thanks.

Patrick
=-=-=-=-=-=-=-=-=-=-=-=-=-
Patrick Jackman
Vancouver, BC

 

[John Spencer]

I get lost with your table names and field names. So I will make up my
own and let you try to sort this out. I think this would be a universal
solution.

Assuming the the Contacts table is joined to the EMailAddresses table on
the ContactID and the EmailAddresses table contains the EmailAddressTypeID.

SELECT Contacts.ContactID, EmailAddresses.EmailAddress
FROM (Contacts INNER JOIN EmailAddresses
ON Contacts.ContactID = EmailAddress.ContactID)
INNER JOIN
(SELECT ContactID, Min(EmailAddressType) AS MinType
FROM EmailAddresses
GROUP BY ContactID) As UseThis
ON EmailAddresses.ContactID = UseThis.ContactID
AND EmailAddress.EmailAddressType = UseThis.MinType

Substitute your table and field names as appropriate.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hello again Allen. I posted a question about this to a SQL Server group and
Erland Sommarskog offered a reply that translates to Access as follows.
Erland's solution takes roughly 2 seconds. My crosstab takes about 1 second.
So in this case "ugly" is winning although I wouldn't want to expand it to
situations where there are more than 3 possible EmailTypes.

SELECT CM.OrgContactID, EC.EAddress
FROM tblCMUsageType AS UT
INNER JOIN
(tblPartyCM AS CM INNER JOIN tblPartyECom AS EC ON CM.PartyCMID =
EC.PartyEComID)
ON UT.CMUsageTypeID = EC.CMUsageTypeID
WHERE (EC.CMCategoryTypeID=5)
AND (EC.ThruDate Is Null)
AND (UT.CMUsageSort=
(SELECT Min([CMUsageSort]) AS MinSort FROM tblPartyCM AS CM2 INNER JOIN
(tblCMUsageType AS UT2 INNER JOIN tblPartyECom AS EC2 ON UT2.CMUsageTypeID =
EC2.CMUsageTypeID) ON CM2.PartyCMID = EC2.PartyEComID WHERE CM2.OrgContactID
= CM.OrgContactID))


SELECT EM.OrgContactID, IIf(Not IsNull([EM].[1]),[EM].[1],IIf(Not
IsNull([EM].[2]),[EM].[2],[EM].[3])) AS EMail
FROM
[TRANSFORM First(tblPartyECom.EAddress) AS FirstOfEAddress
SELECT tblPartyCM.OrgContactID
FROM tblPartyCM
INNER JOIN
(tblCMUsageType INNER JOIN tblPartyECom ON
tblCMUsageType.CMUsageTypeID
=tblPartyECom.CMUsageTypeID)
ON tblPartyCM.PartyCMID = tblPartyECom.PartyEComID
WHERE (tblPartyECom.CMCategoryTypeID=5)
AND (tblPartyECom.ThruDate Is Null)
GROUP BY tblPartyCM.OrgContactID ORDER BY tblCMUsageType.CMUsageSort
PIVOT tblCMUsageType.CMUsageSort In (1,2,3)].
AS EM;

A SQL Server 2008 sample follows (with "tbl" prefixes removed)

DROP TYPE PartyCM;

DROP TYPE PartyECom;

DROP TYPE CMUsageType;

GO

CREATE TYPE PartyCM AS TABLE (

PartyCMID INT NOT NULL,

CMTypeID SMALLINT NOT NULL,

PartyID INT NOT NULL,

OrgContactID INT)

GO

CREATE TYPE PartyECom AS TABLE (

PartyEComID INT NOT NULL,

EAddress VARCHAR(125) NOT NULL,

CMUsageTypeID SMALLINT NOT NULL,

FromDate DATE NOT NULL,

ThruDate Date)

GO

CREATE TYPE CMUsageType AS TABLE (

CMUsageTypeID SMALLINT NOT NULL,

CMUsageTypeName VARCHAR(20) NOT NULL,

CMUsageSort SMALLINT NOT NULL)

GO

DECLARE @UT CMUsageType;

DECLARE @CM PartyCM;

DECLARE @EC PartyECom;

INSERT @UT EXEC('

SELECT 1, ''Business'', 1

SELECT 2, ''Personal'', 3

SELECT 3, ''Bus & Pers'', 2

SELECT 4, ''Emergency'', 4

')

INSERT @CM EXEC('

SELECT 1, 5, 101, 301

SELECT 2, 5, 102, 302

SELECT 3, 5, 102, 302

SELECT 4, 5, 103, 303

SELECT 5, 5, 104, 304

')

INSERT @EC EXEC('

SELECT 1, ''(e-mail address removed)'', 1, ''8/1/2009'', NULL

SELECT 2, ''(e-mail address removed)'', 2, ''8/1/2009'', NULL

SELECT 3, ''(e-mail address removed)'', 1, ''8/1/2009'', NULL

SELECT 4, ''(e-mail address removed)'', 1, ''8/1/2009'', NULL

SELECT 5, ''(e-mail address removed)'', 1, ''1/1/09'', ''8/1/2009''

')

-- Simple output

SELECT OrgContactID, EAddress, CMUsageTypeName

FROM @EC EC

INNER JOIN @CM CM

ON CM.PartyCMID = EC.PartyEComID

INNER JOIN @UT UT

ON UT.CMUsageTypeID = EC.CMUsageTypeID

WHERE (CM.CMTypeID=5) AND (EC.ThruDate IS NULL);

/* 301 (e-mail address removed) Business

302 (e-mail address removed) Personal

302 (e-mail address removed) Business

303 (e-mail address removed) Business

*/


-- Desired output

/* 301 (e-mail address removed) Business

302 (e-mail address removed) Business

303 (e-mail address removed) Business

*/

If one client can have many email address of different types, you need to
store this in 3 tables:

a) tblEmailType (one record for each type, with EmailTypeID as primary
key, where lower numbers mean higher priority.)

b) tblClient (one record for each client, with ClientID as primary key but
no email fields);

c) tblClientEmail, with fields like this:
- ClientEmailID primary key
- ClientID relates to tblClient.ClientID
- Email text

You can then get the preferred email address like this:
SELECT tblClient.ClientID,
(SELECT TOP 1 Email
FROM tblClientEmail
WHERE tblClientEmail.ClientID = tblClient.ClientID
AND tblClientEmail.Email Is Not Null
ORDER BY tblClientEmail.EmailTypeID, tblClientEmail.ClientEmailID)
AS Email
FROM tblClient;

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a set of ContactIDs, EmailAddresses and EmailAddressTypes (1=Bus,
2=Bus&Pers, 3=Pers).

I need to return a list of 1 email address per Contact and where a
Contact
has more than 1 email address, I need to pick the first one sorted by
EmailAddressType. That is, if a Contact has both a type2 and a type3
email
address, then return the type2.

I can do it with this crosstab subquery, but is there a "nicer" way?

SELECT EM.OrgContactID, IIf(Not IsNull([EM].[1]),[EM].[1],IIf(Not
IsNull([EM].[2]),[EM].[2],[EM].[3])) AS EMail
FROM [TRANSFORM First(tblPartyECom.EAddress) AS FirstOfEAddress SELECT
tblPartyCM.OrgContactID FROM tblPartyCM INNER JOIN (tblCMUsageType INNER
JOIN tblPartyECom ON tblCMUsageType.CMUsageTypeID =
tblPartyECom.CMUsageTypeID) ON tblPartyCM.PartyCMID =
tblPartyECom.PartyEComID WHERE (((tblPartyECom.CMCategoryTypeID)=5) AND
((tblPartyECom.ThruDate) Is Null)) GROUP BY tblPartyCM.OrgContactID ORDER
BY tblCMUsageType.CMUsageSort PIVOT tblCMUsageType.CMUsageSort In
(1,2,3)]. AS EM;


Thanks.

Patrick
=-=-=-=-=-=-=-=-=-=-=-=-=-
Patrick Jackman
Vancouver, BC

 

[Patrick Jackman]

Thanks John. Yes the schema is a bit complicated.

I put your solution into action and it performs as well as Erland's. The
resulting SQL is below. I tried to get JETSHOWPLAN "ON" to work in Access
2003 but couldn't find an output file anywhere. I'm using XP SP3.

SELECT tblPartyCM.OrgContactID, tblPartyECom.EAddress
FROM
(([SELECT tblPartyCM.OrgContactID, Min(tblCMUsageType.CMUsageSort) AS
MinSort FROM tblPartyCM INNER JOIN (tblCMUsageType INNER JOIN tblPartyECom
ON tblCMUsageType.CMUsageTypeID = tblPartyECom.CMUsageTypeID) ON
tblPartyCM.PartyCMID = tblPartyECom.PartyEComID GROUP BY
tblPartyCM.OrgContactID]. AS MS
INNER JOIN tblPartyCM ON MS.OrgContactID = tblPartyCM.OrgContactID)
INNER JOIN tblCMUsageType ON MS.MinSort = tblCMUsageType.CMUsageSort)
INNER JOIN tblPartyECom ON (tblPartyCM.PartyCMID = tblPartyECom.PartyEComID)
AND (tblCMUsageType.CMUsageTypeID = tblPartyECom.CMUsageTypeID);