Hi,

I'm working on a simple module and have hit a brick wall with what I presume is a simple db query. Ye I just can't figure it out.

Lets say you have a page that lists a dynamic checkbox list of languages and you want to filter a list of students by the checkboxes selected.

For example, I want to create an SQL query that retreives a list of the names of students who speak English AND French ONLY.   I dont want a query that returns English or French.

I would really appreciate any help with this. There has to be some folks out there that can see this straight way.

Cheers,

Colin

----------------------------------------------------------------------------

Here's a simple table structure example:

Table: Students - StudentId (PrimaryKey)

Table: Languages - LanguageId(PrimayKey)

 Table: StudentLanguage - StudentId, LangugeId (Composite PrimaryKey)

SELECT Students.StudentName FROM Students
INNER JOIN StudentLanguage ON Students.StudentId = StudentLanguage.StudentId
INNER JOIN Languages ON StudentLanguage.LangugeId = Languages.LanguageId
where Languages.LanguageName = 'English' or Languages.LanguageName = 'French'
 

If you need to return result for more that one lanuguge add distinct after Select or it will return a record for each student

Example:

StudentName                                           LanguageName
-------------------------------------------------- --------------------------------------------------
John                                                            English
John                                                            French

You can do a pivot table of the studentlanguage table so that you have a column for each language, then select from the pivot table the combination you are looking for.

If you create this pivot table using a series of joins, I believe you might need to use LEFT JOIN instead of INNER JOIN, or else you risk not picking up all the possible records.