Guest mdcarr Posted May 30, 2006 Share Posted May 30, 2006 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. Link to comment Share on other sites More sharing options...
aspnetguy Posted May 30, 2006 Share Posted May 30, 2006 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. Link to comment Share on other sites More sharing options...
dooberry Posted June 14, 2006 Share Posted June 14, 2006 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now