One Row from Multiple Tables with Multiple Items

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

One Row from Multiple Tables with Multiple Items

Author

Topic

farrell.evans
Starting Member

2 Posts

Posted - 2008-03-11 : 14:57:34

I have two tables, Personnel and Emails.
Personnel
*per_personnelID (int)
per_name (varchar)
per_office (varchar)
per_cell (varchar)

Emails
*eml_emailID (int)
eml_personnelID (int)
eml_name (varchar)

I can have a user that has multiple emails but I need to return them all in one row in addition to the name, office and phone. And I need to do this regardless of the number of emails they have. Please, can anyone help the newbie?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-11 : 20:24:06

select * from personnel p
where
(select count(*) from emails e where p.eml_personnelID = p.per_personnelID)>2

or

select per_personnelID,per_name,per_office,per_cell from personnel
inner join emails e on e.eml_personnelID=p.per_personnelID
group by per_personnelID,per_name,per_office,per_cell from personnel
having count(*)>1

That will give you your people with more than 1 email.
Not sure what you mean 'all in one row' but if you mean one email per column you are doing it wrong.

Nasty naming convention by the way.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-11 : 21:42:56

quote:
Originally posted by LoztInSpace

select * from personnel p
where
(select count(*) from emails e where p.eml_personnelID = p.per_personnelID)>2

or

select per_personnelID,per_name,per_office,per_cell from personnel
inner join emails e on e.eml_personnelID=p.per_personnelID
group by per_personnelID,per_name,per_office,per_cell from personnel
having count(*)>1

The poster needs to return 2 or more rows of emails, in 1 distinct row of records per individual

The naming convention makes sense. Preceding the underscore identifies the table associated...not so bad at all. makes it easy when selecting columns to know "which table" you are getting it from ;)

How many emails might a person have? 2, 3, 10? how many they might have could make the solution required much different..

if only 2, a simple left join to two instances of the email table could do it, but if there are 10 possibilities, there might be a way to build the rows into a string..

Poor planning on your part does not constitute an emergency on my part.

farrell.evans
Starting Member

2 Posts

Posted - 2008-03-13 : 12:17:18

Thanks for your response. In all likelihood, a user will have no more than 4 emails, 2 is the norm but some list as many as 5. The thing is that I am trying to list the information on an ASP.Net page such that:

Jane Doe
Agent in Charge
(555)111-2222
jane.doe@hotmail.com
jane.doe@someagency.mil
jane.doe@someagency.smil.mil

John Doe
Special Agent
(555)111-3333
john.doe@hotmail.com
john.doe@someagency.mil
john.doe@someagency.smil.mil
john.doe@someagency.gov

Thanks for all your assistance.

X002548
Not Just a Number

15586 Posts

Posted - 2008-03-13 : 12:38:55

Sounds like a presentaion issue

But

Have a look hee

http://weblogs.sqlteam.com/brettk/archive/2005/02/23/4171.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources