September 22, 2009 at 8:02 am
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%'))
September 22, 2009 at 8:09 am
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
September 22, 2009 at 8:20 am
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!
September 22, 2009 at 8:41 am
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
September 22, 2009 at 11:16 am
No, it was the full code...should I put an order by in there?
Thanks
September 22, 2009 at 11:27 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply