SQL query for retrieving multiple emails in one record
up vote
0
down vote
favorite
I have these tables
| idEmail | idPerson | email |
| 213 | 1 |abc@abc.com|
| 214 | 2 |def@abc.com|
| 215 | 2 |fed@abc.com|
| idPerson | name | lastName |
| 1 | Joe | Black |
| 2 | Will | Smith |
I would like to retrieve one record for each person showing all mails adresses like this:
| Name | lastName | email 1 | email 2 |
| Joe | Black |abc@abc.com| |
| Will | Smith |def@abc.com|fed@abc.com|
sql row
asked Nov 9 at 1:07
Hugo Jastrzebski
81
add a comment |
up vote
0
down vote
favorite
I have these tables
| idEmail | idPerson | email |
| 213 | 1 |abc@abc.com|
| 214 | 2 |def@abc.com|
| 215 | 2 |fed@abc.com|
| idPerson | name | lastName |
| 1 | Joe | Black |
| 2 | Will | Smith |
I would like to retrieve one record for each person showing all mails adresses like this:
| Name | lastName | email 1 | email 2 |
| Joe | Black |abc@abc.com| |
| Will | Smith |def@abc.com|fed@abc.com|
sql row
asked Nov 9 at 1:07
Hugo Jastrzebski
81
What's your dbms?
– D-Shih
Nov 9 at 1:14
Right now Access 2016, might use Mysql aswell.
– Hugo Jastrzebski
Nov 9 at 1:38
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have these tables
| idEmail | idPerson | email |
| 213 | 1 |abc@abc.com|
| 214 | 2 |def@abc.com|
| 215 | 2 |fed@abc.com|
| idPerson | name | lastName |
| 1 | Joe | Black |
| 2 | Will | Smith |
I would like to retrieve one record for each person showing all mails adresses like this:
| Name | lastName | email 1 | email 2 |
| Joe | Black |abc@abc.com| |
| Will | Smith |def@abc.com|fed@abc.com|
sql row
asked Nov 9 at 1:07
Hugo Jastrzebski
81
I have these tables
| idEmail | idPerson | email |
| 213 | 1 |abc@abc.com|
| 214 | 2 |def@abc.com|
| 215 | 2 |fed@abc.com|
| idPerson | name | lastName |
| 1 | Joe | Black |
| 2 | Will | Smith |
I would like to retrieve one record for each person showing all mails adresses like this:
| Name | lastName | email 1 | email 2 |
| Joe | Black |abc@abc.com| |
| Will | Smith |def@abc.com|fed@abc.com|
sql row
sql row
asked Nov 9 at 1:07
Hugo Jastrzebski
81
asked Nov 9 at 1:07
Hugo Jastrzebski
81
asked Nov 9 at 1:07
Hugo Jastrzebski
81
asked Nov 9 at 1:07
Hugo Jastrzebski
81
asked Nov 9 at 1:07
Hugo Jastrzebski
81
81
What's your dbms?
– D-Shih
Nov 9 at 1:14
Right now Access 2016, might use Mysql aswell.
– Hugo Jastrzebski
Nov 9 at 1:38
add a comment |
What's your dbms?
– D-Shih
Nov 9 at 1:14
Right now Access 2016, might use Mysql aswell.
– Hugo Jastrzebski
Nov 9 at 1:38
What's your dbms?
– D-Shih
Nov 9 at 1:14
What's your dbms?
– D-Shih
Nov 9 at 1:14
Right now Access 2016, might use Mysql aswell.
– Hugo Jastrzebski
Nov 9 at 1:38
Right now Access 2016, might use Mysql aswell.
– Hugo Jastrzebski
Nov 9 at 1:38
add a comment |
2 Answers
2
active
oldest
votes
up vote
0
down vote
You can try to use JOIN then do condition aggregate function.
SELECT Name,
lastName,
MAX(CASE WHEN t1.idPerson = 1 then email end) 'email 1',
MAX(CASE WHEN t1.idPerson = 2 then email end) 'email 2'
FROM t1 JOIN t2 on t1.idPerson = t2.idPerson
group by Name,lastName
answered Nov 9 at 1:13
D-Shih
24.4k61431
add a comment |
up vote
0
down vote
You could look into the PIVOT statement as well or put the set of emails in one column with a comma or other delimiter
answered Nov 9 at 1:24
Jassem Abdal
1
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
You can try to use JOIN then do condition aggregate function.
SELECT Name,
lastName,
MAX(CASE WHEN t1.idPerson = 1 then email end) 'email 1',
MAX(CASE WHEN t1.idPerson = 2 then email end) 'email 2'
FROM t1 JOIN t2 on t1.idPerson = t2.idPerson
group by Name,lastName
answered Nov 9 at 1:13
D-Shih
24.4k61431
add a comment |
up vote
0
down vote
You can try to use JOIN then do condition aggregate function.
SELECT Name,
lastName,
MAX(CASE WHEN t1.idPerson = 1 then email end) 'email 1',
MAX(CASE WHEN t1.idPerson = 2 then email end) 'email 2'
FROM t1 JOIN t2 on t1.idPerson = t2.idPerson
group by Name,lastName
answered Nov 9 at 1:13
D-Shih
24.4k61431
add a comment |
up vote
0
down vote
up vote
0
down vote
You can try to use JOIN then do condition aggregate function.
SELECT Name,
lastName,
MAX(CASE WHEN t1.idPerson = 1 then email end) 'email 1',
MAX(CASE WHEN t1.idPerson = 2 then email end) 'email 2'
FROM t1 JOIN t2 on t1.idPerson = t2.idPerson
group by Name,lastName
answered Nov 9 at 1:13
D-Shih
24.4k61431
You can try to use JOIN then do condition aggregate function.
SELECT Name,
lastName,
MAX(CASE WHEN t1.idPerson = 1 then email end) 'email 1',
MAX(CASE WHEN t1.idPerson = 2 then email end) 'email 2'
FROM t1 JOIN t2 on t1.idPerson = t2.idPerson
group by Name,lastName
answered Nov 9 at 1:13
D-Shih
24.4k61431
answered Nov 9 at 1:13
D-Shih
24.4k61431
answered Nov 9 at 1:13
D-Shih
24.4k61431
answered Nov 9 at 1:13
D-Shih
24.4k61431
24.4k61431
add a comment |
add a comment |
up vote
0
down vote
You could look into the PIVOT statement as well or put the set of emails in one column with a comma or other delimiter
answered Nov 9 at 1:24
Jassem Abdal
1
add a comment |
up vote
0
down vote
You could look into the PIVOT statement as well or put the set of emails in one column with a comma or other delimiter
answered Nov 9 at 1:24
Jassem Abdal
1
add a comment |
up vote
0
down vote
up vote
0
down vote
You could look into the PIVOT statement as well or put the set of emails in one column with a comma or other delimiter
answered Nov 9 at 1:24
Jassem Abdal
1
You could look into the PIVOT statement as well or put the set of emails in one column with a comma or other delimiter
answered Nov 9 at 1:24
Jassem Abdal
1
answered Nov 9 at 1:24
Jassem Abdal
1
answered Nov 9 at 1:24
Jassem Abdal
1
answered Nov 9 at 1:24
Jassem Abdal
1
1
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53218405%2fsql-query-for-retrieving-multiple-emails-in-one-record%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What's your dbms?
– D-Shih
Nov 9 at 1:14
Right now Access 2016, might use Mysql aswell.
– Hugo Jastrzebski
Nov 9 at 1:38