Hi all I'm trying to figure out what I think should be an exists query
What I have is two tables Lookup List has two fields ID (autonumber) and a description ID=1, description=Red ID=2, description=blue ID=3, description=Green ID=4, description=Purple The customer table has a name field and a memo. The memo contains which colour preferences the customer has, customer can choose as many preferences as they like so Name=Graham, Memo=1,2, (Graham prefers red and blue) Name=Fred, Memo=2,3, Name=Sue, Memo=2,4, Name=Chris, Memo=1, What I'm trying to do is end up with this : - Red is preferred by 2 customers Blue is preferred by 3 customers Green is preferred by 1 customer Purple is preferred by 1 customer This table will help the user figure out which items to stock. I know I can do this long hand by creating a temporary cursor but was looking for something easier. I've been messing with : - select distinct description, count(*) from lookup_list where exists (select memo from customers where instr(cast(lookup_list.id as char),memo)>0) group by lookup_list.description order by lookup_list.description But that doesn't want to play. Any pointers would be gratefully received. Thanks! Graham Brown CompSYS Software Solutions BNI Member. The Business Referral Organisation For more information please go to http://www.bni-plains.co.uk --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
