Retrieving just one row of each value

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

Retrieving just one row of each value

Author

Topic

Spidie Man
Starting Member

4 Posts

Posted - 2009-09-27 : 22:34:58

I've got two tables

Company (C_ID, Name)
Email (Em_ID, EmailAddress, C_ID)

The tables keep track of Company Names and the emails addresses associated with each Company. Each company has at least one email address.

How do I get a list of Companies with one of the email addresses?

Lets say I have three companies in the table, Company A, Company B and Company C. Each company has 5 email addresses. What SQL statement will give me this.

Company A | user@companya.com
Company B | user@companyb.com
Company C | user@companyc.com

Is this correct?

SELECT Company.Name, Email.EmailAddress
FROM Company
JOIN Email
WHERE Company.C_ID = Email.C_ID

Another question (this will be useful in answering the above question, I can't figure this out). How do I get an email address for each C_ID from the Email table?

SELECT C_ID, EmailAddress
FROM Email
WHERE { Em_ID is Maximum for each C_ID group ?? }

I figured if I can get a table with an email for each C_ID, then I could JOIN that and Company

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-28 : 05:56:13

try this:


select C.Name, E.EmailAddress
from Company C
join
(select C_ID, max(Em_ID) as Em_ID from Email group by C_ID)dt
 on C.C_ID = dt.C_ID
join Email E
 on E.Em_ID = dt.Em_Id

No, you're never too old to Yak'n'Roll if you're too young to die.

Spidie Man
Starting Member

4 Posts

Posted - 2009-09-28 : 11:10:44

How does the double JOIN work?

You've JOINed Company C and Email E, but you also have C and dt JOINed.

Is this SQL statement too complicated to be used in a normal SQL system, or is everything at this difficulty level?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-28 : 11:16:16

First I wanna know if it works!?

If yes then I will explain why this is not as complicated as it looks.

No, you're never too old to Yak'n'Roll if you're too young to die.

Spidie Man
Starting Member

4 Posts

Posted - 2009-09-28 : 18:37:21

Yes it does work. I checked it with MS SQL Express database.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-29 : 01:43:51

Fine!
Now I will explain what is going on.

select C.Name, E.EmailAddress
from Company C
join
(select C_ID, max(Em_ID) as Em_ID from Email group by C_ID)dt
on C.C_ID = dt.C_ID
join Email E
on E.Em_ID = dt.Em_Id

First we want the maximum Em_ID for each C_ID group

select C_ID, max(Em_ID) as Em_ID from Email group by C_ID

Now we make this a derived table for further use

(select C_ID, max(Em_ID) as Em_ID from Email group by C_ID)dt

Joining this derived table with the Email table gives us the wanted distinct EmailAddress.

Joining the Company with the derived table gives ud the relation from Company to that wanted Em_ID

Hope that is clear enough.

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Spidie Man
Starting Member

4 Posts

Posted - 2009-09-29 : 09:12:08

Perfect, thanks a lot.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-29 : 09:17:39

welcome