[Eclug] OT: SQL question

David Krings <david.krings at snet.net> writes:

> Hi,
>
>         although you whispered I heard you loud and clear. 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

 While what I think you meant to type looks correct (the "FROM
pivientities, pivicategories INNER JOIN pivicategories ON" looks like
a typo), I'd write it as:

 SELECT DISTINCT Entityid
 FROM pivientities ent JOIN pivicategories cat USING(Entityid)
 WHERE 1=1 AND
       ent.Entitytype = 'pic' OR
       cat.Category_name IN ('Alton', 'Bristol', 'Soft')
 ORDER BY ent.Creation_date ASC;

-- 
James Antill -- james at and.org
http://www.and.org/and-httpd