Extracting information problems

neojakey · April 21st, 2010, 07:02 AM

Morning all, I have limited experience with SQL Server, and I have to ask for help as I am unsure how to proceed and I have my client breathing down my neck.

Let me explain. I have membership table that contains a reference to membership categories. A Member can have multiple categories. Table structure is as follows:

**** TABLE NAME = MasterList ****
RecordID INT Auto
FirstName varchar(80)
LastName varchar(80)
There are more than this but is irrelevant at for this problem. The assigned categories table is as follows

**** TABLE NAME = Member_Assigned_Categories ****
MemCatID Int Auto
RecordID int
CatID int

I have another table of category descriptions, that begins with the CatID.

I have some example data:
Table MasterList:
56 Steve Johnson
57 Vince Jeffreys
58 Matt Slone

Table Member_Assigned_Categories:
67 | 58 | 2
68 | 57 | 22
69 | 58 | 22
70 | 56 | 9
71 | 58 | 6

I need to extract all the records from the materlist table that are assigned to categories 2 and 7. How would run write the SQL statement to do this.

I tried this:

Code:

SELECT COUNT(*) AS MemberCount FROM MasterList INNER JOIN  Member_Assigned_Categories ON  MasterList.RecordID=Member_Assigned_Categories.RecordID WHERE  Member_Assigned_Categories.CatID = 6 AND  Member_Assigned_Categories.CatID = 22;

and this:

Code:

SELECT COUNT(*) AS MemberCount FROM MasterList INNER JOIN  Member_Assigned_Categories ON  MasterList.RecordID=Member_Assigned_Categories.RecordID WHERE  Member_Assigned_Categories.CatID IN(6, 22);

But they never worked. I think I need to group the results or have a nested SQL statement. Please help, I am unsure how to resolve this.
Many thanks for your help with this matter

Paul
[email protected]