asked on

Help Limiting Results of SELECT Statement

I have a SELECT Statement as listed below. It should list companies and contacts along with the associated distribution media.

The problem I have is that any number of contacts can have any combination of 5 distribution media - I need to pull out one record for each contact based on a field called DistributionMedia.MediaPriority - the lower values take precedence.

Based on the query below, is this possible?

Regards,
Danny

SELECT CompMaint.CompMaintID, CompMaint.ptProductID, CompMaint.ptProdVerID, CompMaint.ptUserID, Company.CompID,
Company.CompName,
CASE WHEN CompMaint.ptPurMethodID = 2 THEN 'Rental Renewal (Auto Generated)' WHEN CompMaint.ptPurMethodID = 3 THEN 'Subscription Renewal (Auto Generated)'
ELSE 'Renewal (Auto Generated)' END AS OrderRef, DistributionMedia.MediaPriority
FROM CompMaint INNER JOIN
Company ON CompMaint.ptCompID = Company.CompID INNER JOIN
CompanyContact_Link ON Company.CompID = CompanyContact_Link.ptCompanyID INNER JOIN
ContactDistMedia_Link ON CompanyContact_Link.ptContactID = ContactDistMedia_Link.ptContactID INNER JOIN
DistributionMedia ON ContactDistMedia_Link.ptMediaID = DistributionMedia.MediaID
WHERE (CompMaint.ptPurMethodID = 2 OR CompMaint.ptPurMethodID = 3) AND (CompMaint.Renew = 1) AND (CompMaint.OnHold = 0) AND (CompMaint.ptDealerID IS NULL) AND (Company.ptCompType = 2 OR Company.ptCompType = 4) AND (DistributionMedia.MediaID <> 4) AND (DistributionMedia.MediaID <> 5) AND (CompanyContact_Link.ptContactRoleID = 4)

Guy Hengel [angelIII / a3]

please try this:

SELECT cm.CompMaintID, cm.ptProductID, cm.ptProdVerID, cm.ptUserID, c.CompID, c.CompName,
CASE WHEN cm.ptPurMethodID = 2 THEN 'Rental Renewal (Auto Generated)'
WHEN cm.ptPurMethodID = 3 THEN 'Subscription Renewal (Auto Generated)'
ELSE 'Renewal (Auto Generated)'
END AS OrderRef,
dm.MediaPriority
FROM CompMaint cm
INNER JOIN Company c
ON cm.ptCompID = c.CompID
INNER JOIN CompanyContact_Link ccl
ON c.CompID = ccl.ptCompanyID
INNER JOIN ContactDistMedia_Link cdml
ON ccl.ptContactID = cdml.ptContactID
INNER JOIN DistributionMedia dm
ON cdml.ptMediaID = dm.MediaID
WHERE (cm.ptPurMethodID = 2 OR cm.ptPurMethodID = 3)
AND (cm.Renew = 1)
AND (cm.OnHold = 0)
AND (cm.ptDealerID IS NULL)
AND (c.ptCompType = 2 OR c.ptCompType = 4)
AND (dm.MediaID <> 4)
AND (dm.MediaID <> 5)
AND (ccl.ptContactRoleID = 4)
AND dm.MediaID = ( SELECT TOP 1 DistributionMedia dmi
WHERE cdml.ptMediaID = dmi.MediaID
AND (dmi.MediaID <> 4)
AND (dmi.MediaID <> 5)
ORDER BY dmi.MediaPriority ASC
)

LFMSupport

ASKER

That returns the same results as the original query I posted.

Danny

Guy Hengel [angelIII / a3]

I see. Let's refine the query a bit then:

SELECT cm.CompMaintID, cm.ptProductID, cm.ptProdVerID, cm.ptUserID, c.CompID, c.CompName,
CASE WHEN cm.ptPurMethodID = 2 THEN 'Rental Renewal (Auto Generated)'
WHEN cm.ptPurMethodID = 3 THEN 'Subscription Renewal (Auto Generated)'
ELSE 'Renewal (Auto Generated)'
END AS OrderRef,
dm.MediaPriority
FROM CompMaint cm
INNER JOIN Company c
ON cm.ptCompID = c.CompID
INNER JOIN CompanyContact_Link ccl
ON c.CompID = ccl.ptCompanyID
INNER JOIN ContactDistMedia_Link cdml
ON ccl.ptContactID = cdml.ptContactID
INNER JOIN DistributionMedia dm
ON cdml.ptMediaID = dm.MediaID
WHERE (cm.ptPurMethodID = 2 OR cm.ptPurMethodID = 3)
AND (cm.Renew = 1)
AND (cm.OnHold = 0)
AND (cm.ptDealerID IS NULL)
AND (c.ptCompType = 2 OR c.ptCompType = 4)
AND (dm.MediaID <> 4)
AND (dm.MediaID <> 5)
AND (ccl.ptContactRoleID = 4)
AND cdml.ptContactID = ( SELECT TOP 1 cdmli.ptContactID
FROM DistributionMedia dmi
INNER JOIN ContactDistMedia_Link cdmli
ON cdmli.ptContactID = cdml.ptContactID
WHERE cdmli.ptMediaID =
AND (dmi.MediaID <> 4)
AND (dmi.MediaID <> 5)
ORDER BY dmi.MediaPriority ASC
)

LFMSupport

ASKER

Again, that returns the same number of records.

There is actually an error in the statement you posted that I corrected that may be skewing the results. You wrote:

WHERE cdmli.ptMediaID = AND (dmi.MediaID <> 4)
AND (dmi.MediaID <> 5)
ORDER BY dmi.MediaPriority ASC
)

I changed this to read:

WHERE (dmi.MediaID <> 4)
AND (dmi.MediaID <> 5)
ORDER BY dmi.MediaPriority ASC
)

Guy Hengel [angelIII / a3]

Actually, I got the conditions indeed wrong at that place.
Here a corrected version (note that is difficult to suggest such a complex query without sql server or the sample table data ready...)

SELECT cm.CompMaintID, cm.ptProductID, cm.ptProdVerID, cm.ptUserID, c.CompID, c.CompName,
CASE WHEN cm.ptPurMethodID = 2 THEN 'Rental Renewal (Auto Generated)'
WHEN cm.ptPurMethodID = 3 THEN 'Subscription Renewal (Auto Generated)'
ELSE 'Renewal (Auto Generated)'
END AS OrderRef,
dm.MediaPriority
FROM CompMaint cm
INNER JOIN Company c
ON cm.ptCompID = c.CompID
INNER JOIN CompanyContact_Link ccl
ON c.CompID = ccl.ptCompanyID
INNER JOIN ContactDistMedia_Link cdml
ON ccl.ptContactID = cdml.ptContactID
INNER JOIN DistributionMedia dm
ON cdml.ptMediaID = dm.MediaID
WHERE (cm.ptPurMethodID = 2 OR cm.ptPurMethodID = 3)
AND (cm.Renew = 1)
AND (cm.OnHold = 0)
AND (cm.ptDealerID IS NULL)
AND (c.ptCompType = 2 OR c.ptCompType = 4)
AND (dm.MediaID <> 4)
AND (dm.MediaID <> 5)
AND (ccl.ptContactRoleID = 4)
AND cdml.ptContactID = ( SELECT TOP 1 cdmli.ptContactID
FROM DistributionMedia dmi
INNER JOIN ContactDistMedia_Link cdmli
ON cdmli.ptMediaID = dmi.MediaID
WHERE cdmli.ptContactID = cdml.ptContactID
AND (dmi.MediaID <> 4)
AND (dmi.MediaID <> 5)
ORDER BY dmi.MediaPriority ASC
)

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LFMSupport

ASKER

I can appreciate how difficult something like this is without sample data so I'm greatful for your efforts!

I will need to check this tonight - I'll post back in the morning.

Danny

Lowfatspread

its the mediapriority that needs to be minimised per contact ...
not the contactid ....

am i correct..?

LFMSupport

ASKER

Ye, that's correct.

For example, if I was in there with all five media types:

Danny - Email - Priority 1
Danny - CD - Priority 2
Danny - Floppy - Priority 3
Danny - Web Download - Priority 4

I would only want to pull out the email record.

HTH
Danny

LFMSupport

ASKER

As a small follow-up,

The last queries posted by both Lowfatspread and angelIII return the same set of results. Lowfatspread, thanks for the info RE <> and NOT IN - didn't know that!

Thanks for your efforts so far.
Danny