paulCardiff
asked on
Dynamically Pick this field to return based on the contents of another field
I have a table called users, with the following fields i.e.
ID
Email1
Email2
firstname
lastname
This is then joined to a table called company which contains a field called emailPreference
So my sql looks like this, so far...
SELECT Email1, Email2, firstname,lastname, company.emailPreference
FROM [User]
INNER JOIN [Company] ON [User].[CompanyId] = [Company].[CompanyId]
Now lets say that i wanted to return selected email addresses based on the emailpreference field e.g.
Query 1) If company.emailPreference=1 then return All email1 fields
Query 2) If company.emailPreference=2 then return All email2 fields
Query 3) If company.emailPreference=3 then return All email1 and email2 fields
So output for this would need to look something like this, if used against the following data e.g.
1, p@p.com, a@a.com,tim,smith
2, i@i.com, a@a.com,tom,jones
Query 1 would look like this
ID, SelectedEmail
1, p@p.com
2, i@i.com
Query 2 would look like this
ID, SelectedEmail
1, a@a.com
2, a@a.com
Query 1 would look like this
ID, SelectedEmail
1, p@p.com
2, i@i.com
1, a@a.com
2, a@a.com
[NB: notice how email has its own row]
Can anyone please advice the sql involved in this please, i'm assuming its going to involve a case command?
Thanks
P
ID
Email1
Email2
firstname
lastname
This is then joined to a table called company which contains a field called emailPreference
So my sql looks like this, so far...
SELECT Email1, Email2, firstname,lastname, company.emailPreference
FROM [User]
INNER JOIN [Company] ON [User].[CompanyId] = [Company].[CompanyId]
Now lets say that i wanted to return selected email addresses based on the emailpreference field e.g.
Query 1) If company.emailPreference=1 then return All email1 fields
Query 2) If company.emailPreference=2 then return All email2 fields
Query 3) If company.emailPreference=3 then return All email1 and email2 fields
So output for this would need to look something like this, if used against the following data e.g.
1, p@p.com, a@a.com,tim,smith
2, i@i.com, a@a.com,tom,jones
Query 1 would look like this
ID, SelectedEmail
1, p@p.com
2, i@i.com
Query 2 would look like this
ID, SelectedEmail
1, a@a.com
2, a@a.com
Query 1 would look like this
ID, SelectedEmail
1, p@p.com
2, i@i.com
1, a@a.com
2, a@a.com
[NB: notice how email has its own row]
Can anyone please advice the sql involved in this please, i'm assuming its going to involve a case command?
Thanks
P
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.