[Eclug] OT: SQL question

> From: David Krings
> 
> 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]....OR
> DER 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. 

If I understand what you are saying, which I am not sure I do, you need to "or" the conditions for the category table but "and" them with the conditions for the filetable, something like this:

SELECT DISTINCT filetable.ID FROM filetable, categorytable WHERE filetable.ID=categorytable.ID AND filetable.Date='somedate'...AND...AND...(categorytable.category = 'Linux' OR categorytable.category = 'Computer' OR categorytable.category = 'OS')....ORDER BY ....

Or you can simplify this using the IN operator:

SELECT DISTINCT filetable.ID FROM filetable, categorytable WHERE filetable.ID=categorytable.ID AND filetable.Date='somedate'...AND...AND...categorytable.category IN ('Linux', 'Computer', 'OS') ....ORDER BY ....

You could also use a subselect which makes the DISTINCT and join unnecessary but may optimize differently:

SELECT ID FROM filetable WHERE ID IN (SELECT ID from categorytable WHERE category IN ('Linux', 'Computer', 'OS')) AND Date='somedate'...AND....ORDER BY ....

Adding a DISTINCT to the subselect here might change execution performance for better or worse, I'm not sure offhand.

If I misunderstood the question, you could clarify by just showing a few rows from each table and what you want the result of the query to look like. No need for so much explanatory text that way.

Also, just a pedantic style comment, the "table" in the table names "filetable" and "categorytable" is superfluous, you can tell from the context that they are tables. That's like naming your columns "IDcolumn", "categorycolumn" etc. How about just "files" and "categories" for the table names?

Another style tip you might or might not know is you can use an abbreviated reference to the table names in the query:

SELECT DISTINCT f.ID FROM filetable f, categorytable c WHERE f.ID=c.ID AND f.Date='somedate'...AND...AND...(c.category = 'Linux' OR c.category = 'Computer' OR c.category = 'OS')....ORDER BY ....

Note that the alias is defined after the the table name in the FROM clause. You can actually leave the qualifiers on column names off completely if the name of the column is unique among all tables being joined, but I don't really recommend it personally. I'm also can't remember if that's ANSI kosher or just a MySQLism.

David