On Sunday, February 10, 2002, at 05:34 PM, James Carrier wrote:
>
> Surely there must be a better way than this - but it escapes me. In this
> setup the only way to match a specifc value when searching is to use
> the query:
> SELECT dataid,title FROM table WHERE category LIKE '%|4|%'
> Which obviously has a huge performance penalty - and of course you can't
> JOIN against any of these values.
>
> The only other way I thought of was to use a separate table for the
> category entries:
>
> SELECT dataid,title,category FROM table LEFT JOIN table_categories ON
> table.dataid=table_categories.dataid
>
> But in the example above this would return 3 entries, which I don't
> want,
> and I can't select a particular dataid which satisfies more than
> category,
> e.g. has categories 4 and 7 (i.e. for the example above the LIKE
> statement
> would be: WHERE category LIKE '%|4|%' AND LIKE '%|7|%').
create table mytab (myid serial, title text);
create table mytabcats (mytabid int REFERENCES mytab(myid), category
int);
insert INTO mytab (title) values('Title A');
insert INTO mytabcats (mytabid,category) values(1,10);
insert INTO mytabcats (mytabid,category) values(1,11);
insert INTO mytabcats (mytabid,category) values(1,12);
insert INTO mytab (title) values('Title B');
insert INTO mytabcats (mytabid,category) values(2,20);
insert INTO mytabcats (mytabid,category) values(2,21);
insert INTO mytabcats (mytabid,category) values(2,22);
insert INTO mytabcats (mytabid,category) values(2,100);
insert INTO mytabcats (mytabid,category) values(1,100);
select distinct myid, title from mytab left join mytabcats ON myid =
mytabid where category in (10,100);
-- myid | title
--------+---------
-- 1 | Title A
-- 2 | Title B
==============================
David Stanaway
Personal: david@stanaway.net
Work: david@netventures.com.au