Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
KatEyez44
Starting Member
2 Posts |
 Posted - 2006-01-03 : 11:59:03
|
| I am having trouble thinking this one through, sorry so long, but any help would be appreciated:Part of the application that I'm working on tracks an organization's chapters. Chapters can be retired, when this happens, the RetiredChapterID is associated with an ActiveChapterID. We have a separate table that stores RetiredChapterID and ActiveChapterID associations.When a user attempts to search on the organization's members, they have the option of searching by chapter ID. What I need to make happen is that if they search on a currently ActiveChapterID, I need to also search all RetiredChapterID's associated with the ActiveChapterID. If they search on one of the RetiredChapterID's, I need to also search the ActiveChapterID that it's associated with, as well as any other RetiredChapterID's that it's ActiveChapterID is associated with.There is a very big, complicated, existing view with which I have to make this work. So, I'm thinking that what I need to do is a separate query to get the list of chapter ID's I need to search against, and then query the view to pull all member records whose chapter ID is in the first result set. The problem - how do I get that first result set? Every solution I've been able to come up with returns a view with multiple columns, and I think I need for all of those chapter IDs to be in a single column for the IN statement to work. |
|
|
jshepler
Yak Posting Veteran
60 Posts |
Posted - 2006-01-03 : 12:26:11
|
| Not sure I understand correctly, but something like this?[CODE]SELECT MemberFROM MembersWHERE ChapterID = @SearchChapterID OR ChapterID IN ( SELECT DISTINCT ChapterID FROM RetiredChapters WHERE RetiredChapterID = @SearchChapterID )[/CODE]/jeff |
 |
|
|
KatEyez44
Starting Member
2 Posts |
Posted - 2006-01-03 : 13:32:30
|
Thanks, Jeff. That helped - I think I was just making things more complicated than I needed to.  |
|
|
|
|
|
|
|
|
Having trouble...