Sql Group by for rows with all columns the same except one

Question

I have a table with a number of columns that will be used to save searches stored by a user.

Every day an email needs to be sent out containing the latest search results. There could be thousands of saved searches.

My columns in my saved search table are similar to as follows:

Id 
userEmail 
SearchParam1 
SearchParam2 
SearchParam3

As there will be so many searches I anticipate there will be many that are the same so I would like to get every row where param1,2 and 3 are the same but still have access to the users email so I can send the same results to each without having to run the same search multiple times.

Ideally I'd like something like this back:

SearchParam1, SearchParam2, SearchParam3
|
|____Email1
|
|____Email2
|
|____Email3

Kind of grouped by the search params but linked to the emails some how.

I don't even know where to start, hence why I have not added any code. Can anyone give me some suggestions?

EDIT Just to clarify. One email would not exist more than once in the table. So technically they are only allowed to save 1 search each.

There are no users as such, just email addresses.

Bex

Answer 1

What you really should consider is data normalization. This looks like a many-to-many relationship, so a structure like this would be idea:

create table yourUsersTable
(
    id int identity(1, 1) primary key clustered not null,
    name varchar(1000) not null,
    email varchar(1000) not null
)
go

create table searchParams
(
    id int identity(1, 1) primary key clustered not null,
    searchText varchar(1000) not null
)
go

create table userSearchParams
(
    fkYourUsersTable int not null references yourUsersTable(id),
    fkSearchParams int not null references searchParams(id)
)
go

This way, if you wanted to get all the email addresses for users subscribed to a search parameters, it'd be a simple query:

select u.email
from yourUsersTable u
inner join userSearchParams up
on u.id = up.fkYourUsersTable
inner join searchParams p
on p.id = up.fkSearchParams
where p.searchText = 'your search parameter here'

This is a basic example of implementing data normalization with a many-to-many relationship. It utilizes a join table to create the relationship between users and search parameters. Data retrieval has become much simpler with the above design, and you aren't constricted by your original denormalized data (with columns like searchParam1, searchParam2, etc.).

Please let me know if that made sense.