Nested Selects and Subqueries

[Guest mdcarr]

First, let me say thank you in advance for your time.I have three tables: Contacts, Categories and ContactCategories. The purpose of these tables is to manage a list of contacts and which categories each contact is assigned to. Let me give you a layout of the design.Contacts:ContactId, int4, identityContactName, nvarchar30Categories:CategoryId, int4, identityCategoryName, nvarchar30ContactCategoriesContactCategoryId, int4, identityContactId, int4CategoryId, int4Here are some records for each table:Contacts:1, Barry2, MichaelCategories:1, Male2, Golfs3, SwimsContactCategories:1, 1, 12, 1, 23, 2, 14, 2, 25, 2, 3 The first two tables are obvious. There are two contacts and three categories. ContactCategories requires a bit of an explanation. Record 1) Indicates Barry belongs to the 'Male' CategoryRecord 2) Indicates Barry belongs to the 'Golfs' CategoryRecord 3) Indicates Michael belongs to the 'Male' CategoryRecord 4) Indicates Michael belongs to the 'Golfs' CategoryRecord 5) Indicates Michael belongs to the 'Swims' CategoryWith this design I can add contacts and categories at runtime and reassign contacts to a variety of categories.I now have two questions. Q1) I need the sql statement that will find all contacts who play golf OR swim. This sql statement should return two records, namely Barry and Michael.Q2) I need the sql statement that will find all contacts who play golf AND swim. This sql statement should return one record, namely Michael.So the procedure should accept an array of category names and a connector.Public Sub GetContacts(ByRef sCategoryies() as string, ByVal bLinkedByOr as Boolean) Dim sSqlCmd As String sSqlCmd = "place your answer here"End SubHopefully this was clear.Thank you again.

[aspnetguy]

SELECT con.ContactName FROM Contacts conINNER JOIN ContactCategories concatON  con.ContactId = concat.ContactIdINNER JOIN Categories catON cat.CategoryId = concat.CategoryIdWHERE concat.CategoryId=2OR concat.CategoryId=3

This should get the first result you want.

[dooberry]

The above example works great, but you could go one step further.If you don't want to have to know the value of categoryid you could just change the Where clause to

 WHERE categories.categoryname = 'Golf' OR categories.categoryname ='Male'

This is useful if the id is system generated for each category.