acapp
Posts: 31
|
| Posted: 04/17/2004, 8:03 PM |
|
Hi,
What I have are number of records that correspond to books.
i.e. I have 3 copies of such and such a book. However, the book each
have their own record in the database, so you don't know you have three unless you total them. No problem got that figured out, however, now
the grid shows the books three times on the grid. I only want to see the book title once with the total of three. How do I do just show the book title once on the grid.
I have literally hundreds of titles. some with over 30 copies, so right now I'm seeing the same book 30 times.
I'm using PHP and MySQL with CCS 2.2.60. Any help would be appreciated.
Thanks
Andrew 
|
 |
 |
peterr
Posts: 5971
|
| Posted: 04/19/2004, 12:32 AM |
|
You probably should use the SQL 'GROUP BY' statement for this. Please open your Data Source property dialog, change "Data Source Type" from "Table" to "SQL" and then add the GROUP BY statement.
I'm not sure if you're familiar with SQL but if not then here is an example that looks similar to what you described: http://www.1keydata.com/sql/sqlgroupby.html
Also take a look at http://www.google.com/search?q=sql+%22group+by%22
BTW, I first recommended testing your grid after changing "Table" to "SQL". Once this works OK then continue onto experimenting with GROUP BY.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
acapp
Posts: 31
|
| Posted: 04/19/2004, 8:29 AM |
|
Thanks Peter, I didn't understand that I could enter SQL that way in CCS,
now that I do, this will make things so much easier that I thought with the other
Lookups etc.
I used the following SQL by building it in the visual query builder and then modifying it a bit, by putting in the count(*). To get that on the grid I just
selected count(*) as the control source. That's all I need to do. Not bad.
I 'm just giving you this information in case someone in the future has the same questions.
SELECT courses.course_id AS courses_course_id, count(*), course_name, courses.course_primary AS courses_course_primary, courses.course_secondary AS courses_course_secondary,
course_required, training_id, emp_id, training.course_id AS training_course_id, course_primary.*, course_secondary.*, graphics.*
FROM (((courses INNER JOIN training ON
training.course_id = courses.course_id) INNER JOIN graphics ON
graphics.graphic_id = courses.course_required) INNER JOIN course_secondary ON
course_secondary.course_secondary_id = courses.course_secondary) INNER JOIN course_primary ON
course_primary.course_primary_id = courses.course_primary
WHERE course_name LIKE '%{s_keyword}%'
OR course_code LIKE '%{s_keyword}%'
OR course_required LIKE '%{s_keyword}%'
GROUP BY courses.course_id
Thanks Again,
Andrew
|
|
|
|
|
