Help with group by

Question

Help with group by

Magy

SSCarpal Tunnel

Points: 4217
More actions
September 22, 2009 at 8:02 am

#141905

I use the query below to search through 3 different tables as part of a search feature on my website. It works, but I can't get it to group the search results by group, institution, then user. Is there a way to do this with the query below?
(SELECT CAST(groupID AS VARCHAR(50)) AS userID, groupName AS userName, '' AS emailAddress, '' AS institutionName, ISNULL(groupDesc, '') AS Description, 'group' AS memberType FROM groupList
WHERE groupName LIKE '%searchTerm%')
UNION ALL
(SELECT CAST(institutionID AS VARCHAR(50)) AS userID, institutionName AS userName, '' AS emailAddress, '' AS institutionName, '' AS Description, 'institution' AS memberType FROM institutionList
WHERE institutionName LIKE '%searchTerm%')
UNION ALL
(SELECT CAST(A.userID AS VARCHAR(50)) AS userID, lastName + ', ' + firstName AS userName, emailAddress, institutionName,
'' AS Description, 'user' AS memberType
FROM userInfo A
LEFT JOIN institutionList B ON B.institutionID = A.institutionID
WHERE isInstitutionalAccount = 0 AND (lastName LIKE '%searchTerm%' OR firstName LIKE '%searchTerm%'))

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Magy i may have read the requirement wrong, but all i did was wrap your query with parenthesis, give it an alias, and add the grouping...is that what you wanted?

SELECT

MyAlias.userID,

MyAlias.emailAddress,

MyAlias.institutionName,

MyAlias.memberType

FROM (

(SELECT CAST(groupID AS VARCHAR(50)) AS userID, groupName AS userName, '' AS emailAddress, '' AS institutionName, ISNULL(groupDesc, '') AS Description, 'group' AS memberType FROM groupList

WHERE groupName LIKE '%searchTerm%')

UNION ALL

(SELECT CAST(institutionID AS VARCHAR(50)) AS userID, institutionName AS userName, '' AS emailAddress, '' AS institutionName, '' AS Description, 'institution' AS memberType FROM institutionList

WHERE institutionName LIKE '%searchTerm%')

UNION ALL

(SELECT CAST(A.userID AS VARCHAR(50)) AS userID, lastName + ', ' + firstName AS userName, emailAddress, institutionName,

'' AS Description, 'user' AS memberType

FROM userInfo A

LEFT JOIN institutionList B ON B.institutionID = A.institutionID

WHERE isInstitutionalAccount = 0 AND (lastName LIKE '%searchTerm%' OR firstName LIKE '%searchTerm%'))

)MyAlias

GROUP BY

MyAlias.userID,

MyAlias.emailAddress,

MyAlias.institutionName,

MyAlias.memberType

ORDER BY

MyAlias.userID,

MyAlias.emailAddress,

MyAlias.institutionName,

MyAlias.memberType

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Magy SSCarpal Tunnel Points: 4217 More actions · Answer 2

Well it didn't group by group, institution, and then user. They are still all mixed up in the search results. I should have been more clear in what I needed it to do.

Currently the query does return results. It is returning results from 3 different tables(groupList, institutionlist, userInfo). I'm trying to get the search results listed in that order. But right now, it just returns them all out of order.

Maybe I can't even do this since I am using UNION ALL?

Thanks!

Lowell SSC Guru Points: 323518 More actions · Answer 3

the piece you posted does not have an ORDER BY, without it, the data comes back in whatever order is convenient for SQL server to get the data.

The piece you posted doesn't have a GROUP BY either. are you mixing terms for group by when you mean order by?

You might remember, without an ORDER BY, SQL server does not guarantee the order of any data; it might return the data in what looks like random order, but actually, it comes in the order SQL Server was able to get the data the fastest, based on the execution plan...often the data *by coincidence* comes back in the order you wanted it, because SQL used a primary key that kept the data the way you wanted it.

was this a partial code, or do you need to add a order by?

ie ORDER BY groupName ,institutionName ,isInstitutionalAccount

(SELECT CAST(groupID AS VARCHAR(50)) AS userID, groupName AS userName, '' AS emailAddress, '' AS institutionName, ISNULL(groupDesc, '') AS Description, 'group' AS memberType FROM groupList

WHERE groupName LIKE '%searchTerm%')

UNION ALL

(SELECT CAST(institutionID AS VARCHAR(50)) AS userID, institutionName AS userName, '' AS emailAddress, '' AS institutionName, '' AS Description, 'institution' AS memberType FROM institutionList

WHERE institutionName LIKE '%searchTerm%')

UNION ALL

(SELECT CAST(A.userID AS VARCHAR(50)) AS userID, lastName + ', ' + firstName AS userName, emailAddress, institutionName,

'' AS Description, 'user' AS memberType

FROM userInfo A

LEFT JOIN institutionList B ON B.institutionID = A.institutionID

WHERE isInstitutionalAccount = 0 AND (lastName LIKE '%searchTerm%' OR firstName LIKE '%searchTerm%'))

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Magy SSCarpal Tunnel Points: 4217 More actions · Answer 4

No, it was the full code...should I put an order by in there?

Thanks

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 5

Lowell's first post has the Order By. You'll just have to re-arrange the order of fields. Also note that you should be placing the Order By only towards the end in a UNION ALL sql.