[Eclug] OT: SQL question

At 12:36 PM 10/8/2006, you wrote:
>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 ....

So, by using the parantheses I make the ORs to be applied on all those rows 
that the query would give without the entire OR section?

>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 ....

Aha, let's see what I can find about the IN operator .....that looks like 
what I really want to do. I want to find all those rows that have the 
various categories in them in the category table and that correspond via 
the ID file to all those that are in the file table, which is filtered by 
all the ANDs.

>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.

I'm not too concerned about performance of the query, as I am sure that the 
execution time and transfer of the page data drastically exceeds the query 
run time. So if I am looking at optimizing the system I'd take a closer 
look at my PHP code than the SQL queries.

>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?

I know, I filled in dummy names to craft an example. I attached csv files 
for the two tables. The Level column is used for limiting which of the 
system users can see what, including having only a subset of categories to 
choose from.


>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.


I read about this and it is just one more thing to worry about. I admit it 
makes things easier and provides better overview.

I will try the IN thing and see that I add some better test records to my 
tables. I let you know what I come up with.


Thank you very much!! 
-------------- next part --------------
"Entityid","Entitytype","English_name","German_name","English_desc","German_desc","Creation_date","Filepath","Views","Last_viewed","Who_last_viewed","Added","Level"
1,"pic","Alton","Alton","Alton eng","Alton deu","2006-02-07 19:12:05","2006\02\PDRM0661.JPG",1,"2006-09-27 19:54:56",1,"2006-09-27 19:54:56",2
2,"pic","Blanket","","","","2006-09-07 20:59:55","2006\09\003104d2e0b1.jpg",1,"2006-10-07 21:00:14",1,"2006-10-07 21:00:14",4
-------------- next part --------------
"Catrecid","Category_name","Entityid","Level"
1,"Alton",1,2
2,"Bristol",1,4
3,"Soft",2,4