I've got a problem with a query that I am putting together. I have two related tables. One containing keywords (Assigned_Subject_Keywords_Table) related to records in the first table (Title_table). I am trying to search for records in the Title_table that may be assigned to multiple subject keywords using an AND operator. Each time I run the query it doesn't return any records. However if I substitute the AND operator for a OR operator it works fine. However this is obviously not what I am after. I need to return only those records assigned to multiple subject keywords.
SELECT DISTINCTROW Title_table.* FROM Title_table LEFT JOIN Assigned_Subject_Keywords_Table ON Title_table.TitleID = Assigned_Subject_Keywords_Table.TitleID WHERE Assigned_Subject_Keywords_Table.SubjectID = 220 AND Assigned_Subject_Keywords_Table.SubjectID = 84
it has been suggested to me to incorporate an aggregate function and loof for records with a count > 1. However I am a bit unsure how to do this.
Many thanks in advance for your assistance.