Re: SQL Help - multi values

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