December 18, 2003 at 3:57 am
I have to pull off a report of people in the current database by selection criteria for an appeal.
I need to pull off name and address etc, and which criteria they fell into...
The problem is people can belong to more than one criteria
e.g
Life Members
Live Donors
Lapsed Donors
Live Annual Members
Lapsed annual Members
Complimentary Members (different to Life Members)
So a person could be a life member and a lapsed donor, but I want the report to pull them off once, with the selection criteria saying the highest category i.e life member. Some categories are mutually exclusive - like Live Donors and Lapsed Donors....
I am not really sure how to do this. I thought about creating a view for each category, and then doing a NOT EXISTS as I go, but this seems quite a long-winded way to do it. I tried to do a UNION statement, but it pulled out duplicates as the selection criteria is unique for each category.
December 18, 2003 at 6:11 am
You really need to redesign your schema. This is a many-to-many relationship between members and categories. You should have a MemberID (int identity?) for each member and one row for each member. Then have another table for member categories with columns for CategoryID (tinyint identity?), description, and priority. Then an association table with MemberID and CategoryID columns. In the meantime, you could try something like this:
SELECT MIN(c.lvl), m.Name, m.Address
FROM Members m JOIN
(SELECT 'Life Member' Category, 1 lvl
UNION ALL SELECT 'Live Donor', 2
UNION ALL SELECT 'Lapsed Donor', 3
UNION ALL SELECT 'Live Annual Member', 4
UNION ALL SELECT 'Lapsed Annula Member', 5
UNION ALL SELECT 'Complimentary Member', 6) c ON c.Category = m.Category
GROUP BY Name, Address
--Jonathan
--Jonathan
December 19, 2003 at 4:35 am
I didn'tdesign the schema - I inherited it. We do have a members table with a memberid for each person and a supporter number linking back to the original supporter table.
If I were to add a priority column to the member type - how would I select to exclude people from being in more than one category?
December 19, 2003 at 7:46 am
quote:
I didn'tdesign the schema - I inherited it. We do have a members table with a memberid for each person and a supporter number linking back to the original supporter table.
I have no idea what a "supporter" table is...
quote:
If I were to add a priority column to the member type - how would I select to exclude people from being in more than one category?
Could you please explain in more detail? Do you mean that this is not a many-to-many relationship?
If you are still referring to the desired result set, and your schema is similar to what I suggested, then perhaps something like this:
CREATE TABLE Members(
MemberId int IDENTITY PRIMARY KEY,
Name varchar(30) NOT NULL,
Address varchar(40))
CREATE TABLE Types(
TypeId tinyint IDENTITY PRIMARY KEY,
Descr varchar(20),
Priority tinyint NOT NULL UNIQUE)
CREATE TABLE Members_x_Types(
MemberId int REFERENCES Members,
TypeId tinyint REFERENCES Types,
PRIMARY KEY (MemberId,TypeId))
INSERT Members(Name)
SELECT 'Joe'
UNION ALL SELECT 'Steve'
UNION ALL SELECT 'Harry'
UNION ALL SELECT 'Moe'
UNION ALL SELECT 'Lucy'
UNION ALL SELECT 'Mary'
INSERT Types(Descr,Priority)
SELECT 'Life Member' Category, 1
UNION ALL SELECT 'Live Donor', 2
UNION ALL SELECT 'Lapsed Donor', 3
UNION ALL SELECT 'Live Annual Member', 4
UNION ALL SELECT 'Lapsed Annula Member', 5
UNION ALL SELECT 'Complimentary Member', 6
Insert Members_x_Types
SELECT 1,3
UNION ALL SELECT 2,2
UNION ALL SELECT 2,5
UNION ALL SELECT 2,3
UNION ALL SELECT 3,1
UNION ALL SELECT 3,6
UNION ALL SELECT 4,5
UNION ALL SELECT 5,1
UNION ALL SELECT 5,2
UNION ALL SELECT 5,3
UNION ALL SELECT 5,4
UNION ALL SELECT 5,5
UNION ALL SELECT 5,6
UNION ALL SELECT 6,4
SELECT
(SELECT TOP 1 t.Descr
FROM Members_x_Types x JOIN Types t ON x.TypeId = t.TypeId
WHERE x.MemberId = m.MemberId
ORDER BY t.Priority) MemberType,
Name, Address
FROM Members m
--Jonathan
--Jonathan
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply