1

Well i have two tables first for employees and the second for emails, i want to see the employees with all his emails

table employees:
id | name  | idE
 1 | name1 | 1
 2 | name2 | 2
 3 | name3 | 3

table email:
id | idE | email
 1 | 1   | e1@email.com
 2 | 1   | e2@email.com
 3 | 2   | e@email2.com
 4 | 3   | e@email3.com

and i have this query:

select e.id, e.name, m.email from employees as e inner join email as m on p.idE = m.idE

and this is the result:

id | name  | email
 1 | name1 | e1@email.com
 1 | name1 | e2@email.com
 2 | name2 | e@email2.com
 3 | name3 | e@email3.com

i dont need employee 1 twice i just need both emails in the same row

Hi guys i modified my query to: select em.name, STUFF( (select ',' + e.Email from tbl_Employees as m inner join tbl_Email as e on m.idE = e.idE for xml path('')) ,1,1,'') as emails from tbl_employees as em

but now my problem is that i get this in each column of email:

e1@email.com,e2@email.com,e@email2.com,e@email3.com,

how can i do it to get only the emails of each employee?

instead of having every single email in each column

for example:

name | email
name1 | e1@email.com,e2@email.com,
name2 | e@email2.com,
name3 | e@email3.com,

pd: sorry if i have some mistakes im still learning english

Ezequiel
  • 79
  • 1
  • 8
  • 1
    Which database are you using? Each has a different syntax for this, but you can look up the equivalent of `group_concat` for any database... – sgeddes Jun 28 '16 at 21:48
  • You can use `stuff` if you use `Sql Server` – Mahedi Sabuj Jun 28 '16 at 22:01
  • also this is a fairly common answer you can search fro concatenate row values into string or something like that and you should find what you are looking for – Matt Jun 28 '16 at 22:16
  • 1
    Possible duplicate of [display more than one value using a SQL query](http://stackoverflow.com/questions/32807451/display-more-than-one-value-using-a-sql-query) – Sam Segers Jun 28 '16 at 22:17
  • im using sql thank you very much for all the replys, im gonna see how stuff works and gruop_concat – Ezequiel Jun 29 '16 at 00:14
  • i have tried with select distinc like this select em.name, STUFF( (select distinct ',' + e.Email from tbl_Employees as m inner join tbl_Email as e on m.idE = e.idE for xml path('')) ,1,1,'') as emails from tbl_employees as em but im still having the same result i have every single email in each column – Ezequiel Jun 29 '16 at 16:27

2 Answers2

4

Although what you're asking for is possible, and I'm sure you'll get several answers, your question is a good example of be careful what you wish for ! You're asking the DBMS to supply some presentation-level functionality. You're usually better off letting SQL find the data you need, and handling presentation issues in your application.

SELECT returns a table of information, and the value of each column in a row is normally "atomic" in the sense that it holds a single value that SQL can compare to other values. The tables in your database have the same property if they adhere to first normal form.

One of the virtues of SQL is that "everything is a table": you can stack SELECTs one atop the other. 

select * from (select a, b from T where a > b) as Z

Each inner one produces another table that the next outer one can act on.

Functions like group_concat (as an example) produce a non-atomic column. It's only a short step from there to code like:

select * from ( select group_concat(a) as a_list from T ) as Z
where a_list like '%Henry%'

The programmer is forced into search strings instead of direct comparison. The query becomes less precise and thus error-prone.

The fail-safe approach, in your example, is to read the results one by one from the DBMS, and concatenate a per-user address list by watching the idE column. It will keep your query simpler, and if you later decide to present them another way, you can do that without diving into the database logic.

James K. Lowden
  • 7,574
  • 1
  • 16
  • 31
  • There are plenty of times I need to concatenate values, and while presentation layers are great for this things like Excel where a ton of SQL data ends up is not good at this particular function. – Matt Jun 28 '16 at 22:32
0

this is my query after the changes

select em.name,
STUFF( 
  (select  ',' + e.Email from tbl_Employees as m inner join 
  tbl_Email as e on m.idE = e.idE 
  where em.idE = e.idE
  for xml path('')
) ,1,1,'') as emails 
from tbl_employees as em

and the results of this query

name | emails
name1 | e1@email.com,e2@email.com
name2 | e@email2.com
name3 | e@email3.com

thank you for the answers!

Ezequiel
  • 79
  • 1
  • 8