Link to home
Start Free TrialLog in
Avatar of paulCardiff
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
SOLUTION
Avatar of tigin44
tigin44
Flag of Türkiye image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial