General information
Category:
Coding, syntax & commands
Jay and Alan,
The reasons for doing my query are very different than the customers with accounts example shown above. Here is what I was doing in VFP 6 and why I need to return the MEMO field as part of a query that has a GROUP BY.
My one to many relationship deals with scripts and phrases. A script contains a bunch of phrases. I am displaying a grid that shows each script with the number of phrases (a COUNT) for each script. So, what I have is:
SELECT Scripts.sc_code, ..., Scripts.sc_comments, COUNT(Phrases.ph_script_id) FROM data!scripts LEFT OUTER JOIN data!phrases ON Scripts.sc_id = Phrases.ph_script_id WHERE Scripts.sc_project == ?parentpkvalue GROUP BY Scripts.sc_code
So, the reason for the GROUP BY is so I can count the number of phrases that belong in a script. The sc_comments field is the MEMO field which I want to display in my grid. Since I am grouping by scripts (sc_code) I know that my comments field is the correct one for that script. Is there any better way to do this, or is my need to use a MEMO with a GROUP BY justified?
Thanks.
Cesar
>Cesar --
>
>Alan has responded to this in several message, so I won't go into the SQL issues.
>
>You're finding the customers that have accounts, I assume.
>
>Is the memo related to the customer? If so, then perhaps your data design would benefit from a customer table with a key into the accounts table. The customer table would maintain this memo information and would be able to report it with a join condition to your query on the account table.
>
>I wouldn't assume that you'd be querying a memo related to the account, since your concern is customers, but perhaps that is the case.
>
>Some thoughts...
>
> Jay
>
>>What would you do if you had a MEMO field as part of the non aggregate statement in VFP7. I can't return MAX of that. Nor can I add that to the GROUP BY statement. Is there any other way to make it aggregate?
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only