Graham,
This does what you want - I think!
As long as the colour Id's are one digit long - mod accordingly if 2 digits.
Dave Crozier
************
* Start Code
Create Cursor curCustomer (Name C(20), preference M)
Insert Into curCustomer Values ("Graham", "1,2")
Insert Into curCustomer Values ("Fred", "2,3")
Insert Into curCustomer Values ("Sue", "2,4")
Insert Into curCustomer Values ("Chris", "1")
Create Cursor curLookup (Id I, Description C(20))
Insert Into curlookup Values (1, "Red")
Insert Into curlookup Values (2, "Blue")
Insert Into curlookup Values (3, "Green")
Insert Into curlookup Values (4, "Purple")
Select all ;
curLookup.Description, ;
Count(*) ;
>From curLookup ;
Join curCustomer On .T. ;
Where At(Alltrim(Transform(curLookup.Id, "@RL 9")),
curCustomer.Preference)<>0 ;
Group By curLookup.Description ;
Into Cursor curResult
Browse
*
* End of Code
*************
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Graham Brown (CompSYS)
Sent: 24 April 2008 22:43
To: ProFox Email List
Subject: [NF] Exists query from mySQL table
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
---
[excessive quoting removed by server]
_______________________________________________
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.
