Dave Crozier wrote:
> 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.
>
Graham. Have you noticed your troubles stem from your tables not being
normalized? They could have been :
Lookup
======
Id description
-- -----------
1 red
2 blue
3 green
4 purple
Customer
========
Id name
-- ----
1 Graham
2 Fred
3 Sue
4 Chris
Preferences
===========
ColId CustId
----- ------
1 1
1 2
2 2
2 3
3 2
3 4
4 1
Then you could have :
Select Lk.description as 'Color', count(*) as 'Amt_Custs'
from Preferences Pref
inner join Lookup Lk
on Lk.Id = Pref.CustId
group by Lk.description
Much simpler statement (of course Lookup.description would have to be a
candidate key).
Ricardo (A.K.A.: SmartAss)
_______________________________________________
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.
