"middletree" <middletree@htomail.com> wrote in message
news:%23TFrJWX0DHA.2288@TK2MSFTNGP10.phx.gbl...[color=blue]
> Sorry for the non-descriptive subject line.
>
> If you go to
http://www.middletree.net/shape.asp, you'll see that I[/color]
have a[color=blue]
> form to fill out.(Ignore the prefilled names and other data; it's just[/color]
for[color=blue]
> testing) When this is filled out, there will be a row in the master[/color]
table,[color=blue]
> called Personal, and there are also static tables for those 4 checkbox[/color]
areas[color=blue]
> you see, called Area, Gift, Ability, and People. Because users can[/color]
check[color=blue]
> more than one box, I resolved the many-to-many by created a union[/color]
table[color=blue]
> (join? composite? not sure what to call that type of table);
>
> Anyway, there are 4 combo tables, PersonalPeople, PersonalGift,
> PersonalAbility, and PersonalArea. As the names imply, each only has 2
> fields: the PK of the Personal table, and the PK of one of the other 4
> tables.
>
> Now, the question: If you go to
http://www.middletree.net/list.asp,[/color]
you'll[color=blue]
> see that it displays a list of all users who have filled out the form.
>
> At the bottom, I have started adding a form that lets you refine the[/color]
search[color=blue]
> and give you a similar list, but one which meets the criteria[/color]
selected. In[color=blue]
> other words, if you select Leadership under the Gifts dropdown, and[/color]
click[color=blue]
> the button (which is not there yet), it should show you all people who
> chcked the Leadership checkbox, regardless of what else they selected.[/color]
But[color=blue]
> if you select Leadership from that dropdown, and College from the[/color]
People[color=blue]
> Groups dropdown, then it would give you only those who selected[/color]
Leadership[color=blue]
> and College.
>
> Problem is, I am not sure how to code the SELECT statement to do this.
> I guess it has to be a join, but not sure how to do it. I have to[/color]
leave[color=blue]
> open the possibility that someone might leave one or more dropdowns
> unselected. I have done joins before, but am drawing a blank on this[/color]
one.[color=blue]
>
> FWIW, this is using Access 2000.[/color]
[uspPersonalSearch]
PARAMETERS
prmAbilityID Long,
prmAreaID Long,
prmGiftID Long,
prmPeopleID Long
;
SELECT
P.PersonalID,
P.PersonalName
FROM
Personal AS P LEFT JOIN
(
SELECT
PersonalID
FROM
(
SELECT PersonalID FROM PersonalAbility WHERE AbilityID = prmAbilityID
UNION ALL
SELECT PersonalID FROM PersonalArea WHERE AreaID = prmAreaID UNION ALL
SELECT PersonalID FROM PersonalGift WHERE GiftID = prmGiftID UNION ALL
SELECT PersonalID FROM PersonalPeople WHERE PeopleID = prmPeopleID
) AS U
GROUP BY
PersonalID
HAVING
COUNT(*)=ABS((prmAbilityID<>0)+(prmAreaID<>0)+(prm GiftID<>0)+(prmPeopleI
D<>0))
) AS F
ON
P.PersonalID = F.PersonalID
WHERE
F.PersonalID IS NOT NULL OR
prmAbilityID + prmAreaID + prmGiftID + prmPeopleID = 0
Notes:
1. You will need to specify a value of 0 (zero) for "All" selections in
your ASP/HTML code
2. You will need to modify the above to correspond to your own naming
conventions
3. In the future, please provide sufficient DDL (CREATE TABLE,CREATE
INDEX, etc...) to reproduce you database environment
HTH
-Chris Hohmann
