[Eclug] OT: SQL question

Hi,

         thank you for looking at this.

At 02:21 PM 10/8/2006, you wrote:
> > From: David Krings
> >
> > Using inner join seems to generate the desired result. A
> > sample query is this:
> >
> > SELECT DISTINCT pivientities.Entityid FROM pivientities,
> > pivicategories INNER JOIN pivicategories ON
> > pivientities.Entityid=pivicategories.Entityid
> > WHERE 1=1 AND pivientities.Entitytype = 'pic' OR
> > pivicategories.Category_name='Alton' OR
> > pivicategories.Category_name='Bristol' OR
> > pivicategories.Category_name='Soft' ORDER BY
> > pivientities.Creation_date ASC
>
>I don't think that's really what you want.
>
>You are doing a three-product cartesian join there, pivientities x 
>pivicategories x pivicategories, which means that you are producing 
>(number of rows in pivicategories) duplicate rows of each row you really 
>do want. The only reason you are not seeing them is because of the DISTINCT.


I left the distinct out and did see the additional rows, which I didn't 
mind too much, since they are gone with distinct in the query.

>Also, the order or precidence in SQL of AND and OR is like pretty much all 
>other languages, AND binds more tightly than or, so your WHERE condition 
>is executed as:
>
>(1=1 AND pivientities.Entitytype = 'pic') OR
>pivicategories.Category_name='Alton' OR
>pivicategories.Category_name='Bristol' OR
>pivicategories.Category_name='Soft'
>
>That is, you will get all rows of the product where Category_name is 
>'Alton' or 'Bristol' or 'Soft' _plus_ all rows where Entitytype is 'pic'. 
>That again, maybe that is what you want.

Thank you for pointing this out. This is not what I want. I want those rows 
whose entity type is pic and that have the categories Alton, Bristol, or 
Soft. I did not notice this as I currently have only 'pic' types in my 
table. So, maybe this isn't really a great test set to begin with.
I looked up the operator precedence and found a table that lists levels 
from 1 through 9 and the corresponding operators. OR was level 9 and AND 
was level 8. The description stated that the "higher level operators have 
precedence over lower level ones". It did not explain if level 1 or level 9 
is the highest level. So, based on what you write AND is preferred over OR.

Hmmm, now I know that the query I have is no good, but I'm still unclear on 
the concept on what I'd have to do. Maybe may description of what I want to 
accomplish is as fuzzy?

David