where statements with wildcards for field names
I am trying to create a dynamic Web site with minimum pages. I am coding in cold fusion.
I have two tables: Subjects, Products
I am creating a dynamic navigation system that pulls all of the subjects and their unique ID's.
For the product pages, I am using the table Products.
I have three fields titled "Subject1, Subject2, Subject3" that is linked to the Subjects table.
How can I create a wildcard in the where statement for a field name?
I bascially want to say, show me the results of products where the value for SubjectID appears in either of the following fields: Subject1 or Subject2 or Subject3 of a product record.
<CFQUERY datasource="art_collection" name="Query1">
Select * From Products
Where Subject1 or Subject2 or Subject3 ='#SubjectID#'
I know I need to somehow transform the field name into a wildcard Subject_
You shouldnt have repeating columns (Subject1, Subject2, Subject3). When there is a one to many relation between products and subjects, that should be stored in a separate table.