?
Solved

Dynamically Pick this field to return based on the contents of another field

Posted on 2009-12-30
2
Medium Priority
?
233 Views
Last Modified: 2012-05-08
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
0
Comment
Question by:paulCardiff
2 Comments
 
LVL 26

Assisted Solution

by:tigin44
tigin44 earned 400 total points
ID: 26144652
here
SELECT CASE WHEN  emailPreference = 1 THEN  Email1
			WHEN  emailPreference = 2 THEN  Email2
			ELSE Email1 
		END AS Email1, 
		CASE WHEN emailPreference = 3 THEN Email2
			 ELSE '' 
	    END AS Email2,
		 firstname,lastname, company.emailPreference
FROM [User]
INNER JOIN [Company] ON [User].[CompanyId] = [Company].[CompanyId]

Open in new window

0
 
LVL 5

Accepted Solution

by:
DerZauberer earned 1600 total points
ID: 26144706
Since you want each Adress on a separate row i would try like this:

SELECT ID, CASE
WHEN emailPreference = 1 THEN Email1
WHEN emailPreference = 2 THEN Email2
WHEN emailPreference = 3 THEN Email1
END as SelectedEmail,
             firstname,lastname, company.emailPreference
FROM [User]
INNER JOIN [Company] ON [User].[CompanyId] = [Company].[CompanyId]
UNION
SELECT ID, Email2,
             firstname,lastname, company.emailPreference
FROM [User]
INNER JOIN [Company] ON [User].[CompanyId] = [Company].[CompanyId]
WHERE emailPreference = 3
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question