Hi, knowing that pretty much anyone on this list knows more than me, I figured you might help me with an SQL problem or at least know where I could find some better place to ask the questions. Well, here is what I want to do. I want to find records in a table that have to have an array of characteristics and on top of that fall into one or more categories. I am struggling with the category portion of a select statement. To put this into concept, I have a table with records for picture and video files. I want to search in that table based on date, type, and so on. That is the easy part, which I got to work without a problem. I have an additional table that contains references to the records in the picture/video table and lists the categories that were associated with each file. A file can have one or more categories assigned, which means that the category table can hold multiple entries for the same file. The search in the categories is not to be mutually exclusive. So I want to craft a select statement that looks like this with a simple join: SELECT DISTINCT filetable.ID FROM filetable, categorytable WHERE filetable.ID=categorytable.ID AND filetable.Date='somedate'...AND...AND...[categoryclause]....ORDER BY .... I left out the dozens of ANDs that I have in my query as a space savings measure. I am really not concerned about these ANDs, I am stuck as to how to craft the categoryclause. The categorytable has records as follows: ID Category 4 Linux 4 Computer 4 OS 4 Desktop Images How do I get the query to include the ID in the resultset when I want to search for all those files that fall into the categories Linux, Computer, OS? I tried using AND, which obviously fails as there is no entry in the category table that suits all requirements at the same time. I tried OR and that basically gives me all those entries that have any of the desired categories assigned regardless if all the AND requirements apply or not. That is obvious as well since OR means exactly that. And that is the point where my rookie knowledge of SQL runs out. I took my SQL book and read up about subqueries. While this seems to be the right thing to do I have no clue as to where I should place that subquery within the entire query. At the place where I indicated the categoryclause and use AND to tie it in? Stick it into the join on the ID fields between the tables? Is there a better way to design the tables, especially the category table? My table design is based on the need that I have to read the categories idependently from the file table entries. I allow for up to 10 categories per file and I could have added 10 columns to the file table, but that seems to be a very dumb and unflexible approach. Any tip is greatly appreciated. It for sure will make me less dumb. It is really fascinating how sth so trivial can be so complicated. David K.
More information about the ECLUG mailing list