[Eclug] OT: SQL question

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

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.

David