Appropriate use of GROUP BY?

[jenn_13]

Using ASP and an Access DB, I have a query that is supposed to get a list of businesses to display in a directory. The table of businesses has multiple records for the same business if they have multiple contact persons. But, I need to "collapse" these multiple listings into single listings that include all the contact people. Here is an example of a query my code generates based on the user searching for "fitness":SELECT * FROM Members INNER JOIN Levels ON Levels.MemberId = Members.MemId WHERE Company LIKE '%fitness%' OR BusCat1 LIKE '%fitness%' OR BusCat2 LIKE '%fitness%' OR BusCat3 LIKE '%fitness%' OR BusCat4 LIKE '%fitness%' OR BusCat5 LIKE '%fitness%' OR RepName LIKE '%fitness%' ORDER BY MemLevel DESC, CompanyThe Members table has most of the columns, and I can't change it at all, and the Levels table I added because there were 2 more columns I needed to make my listings, basically to change the appearance of the listing based on the membership level of the company. If there are, say, 4 different contact persons (RepName) for a business, there will be 4 records for that business, and as it is, that business would be listed 4 times. I need now for it to list the business once, and list all 4 of the representatives in that one record, and I'm not sure if I should be trying to do this in my query, possibly using GROUP BY Company somehow, or if it would be better to just try to do this in my code, which I'm not sure how I'd do either.My code basically just loops through the recordset, generating HTML display of each listing.Also, since we can't count on every business having a record in the Levels table, because they are updated independently, I have to change the INNER JOIN to a LEFT OUTER JOIN, but as soon as I do that, I get this error:Microsoft Cursor Engine (0x80004005)Data provider or other service returned an E_FAIL status.I read somewhere that not using CursorLocation of adUseClient will make that error go away. It indeed does, however, I need that CursorLocation setting for the pagination of my search results.If anyone has any suggestions for solving either of these problems, I thank you in advance

[justsomeguy]

You can only use group by with aggregate functions like average, sum, count, etc. Think of the structure of the recordset that gets returned. It wouldn't make sense to have one row that has a certain ID, and then the next field contains 4 values. A field can't contain more then one value, it only has one. You will need to check in your code for the same member that was previously seen and add the contact information to the same HTML row instead of starting a new row. You can also do some pre-processing on the result set and create your own data structure of all of the companies first using something like a scripting dictionary or an array. When you do that you can look up each contact's company in the dictionary or array first and add them to the existing record if it's there, and then loop through that data structure to display them on the page.As far as the cursor type goes, you just can't use that cursor if you want to use a left inner join. If you need to use the join then you'll need to find another way to do pagination. Unfortunately pagination in Microsoft databases isn't very easy.

[jenn_13]

Thanks for the response! As far as the grouping, I kind of suspected as much myself, "GROUP BY" was suggested by someone else at work. Did I say "LEFT INNER JOIN" when I meant "LEFT OUTER JOIN"? oops. But yeah, I think I'll have to find another way to paginate. I really appreciate the help/confirmation there. I'm really new to this stuff.

You can only use group by with aggregate functions like average, sum, count, etc. Think of the structure of the recordset that gets returned. It wouldn't make sense to have one row that has a certain ID, and then the next field contains 4 values. A field can't contain more then one value, it only has one. You will need to check in your code for the same member that was previously seen and add the contact information to the same HTML row instead of starting a new row. You can also do some pre-processing on the result set and create your own data structure of all of the companies first using something like a scripting dictionary or an array. When you do that you can look up each contact's company in the dictionary or array first and add them to the existing record if it's there, and then loop through that data structure to display them on the page.As far as the cursor type goes, you just can't use that cursor if you want to use a left inner join. If you need to use the join then you'll need to find another way to do pagination. Unfortunately pagination in Microsoft databases isn't very easy.