SQL Select problem

[jhsing]

I want to know if this is possible in SQL. I have 2 tables, an address table and a table where an address may have a number of emailhost associated with it.

I have 2 tables Address and Emails
Address Table
addrid name city
--------------------
1 Joe Seattle
2 Jane Boston
3 John Seattle

Emails Table
emailid addrid emailhost
----------------------
1 1 hotmail.com
2 2 hotmail.com
3 2 yahoo.com
4 1 yahoo.com
5 3 yahoo.com

I want an SQL that will be able to locate the addrid of the person who matches city of Seattle, and email host of yahoo.com and hotmail.com. I would expect the answer to be addrid 1.

What is a good way using SQL to select out emailid's from the email table given a set of emailhosts. I want to be able to answer the question which emailid has emailhosts on yahoo.com only?

thanks

 

[tlbroadbent]

Select Distinct AddrID, Name
From Address Inner Join Emails
On Address.AddrID=Emails.AddrID
Where Address.city='Seattle'
And Emails.emailhost In ('yahoo.com','hotmail.com') Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[carp]

This appears to work; somebody else will probably have something a little more elegant:

SQL> select a.name, a.city, e.emailhost
2 from address a, emails e
3 where a.addrid = e.addrid
4 and a.addrid in (select addrid, count(*)
5 from emails
6 group by addrid
7 having count(*) > 1)
8 and a.city = 'Seattle';

 

[carp]

Correction:

SQL> select a.name, a.city, e.emailhost
2 from address a, emails e
3 where a.addrid = e.addrid
4 and a.addrid in (select addrid
5 from emails
6 group by addrid
7 having count(*) > 1)
8 and a.city = 'Seattle';

Sorry - cut/pasted the wrong piece of code!

 

[pranav777]

select addrid from email
where emailhost = 'yahoo.com'
and
addrid in (select addrid from address where city = 'Seattle')
intersect
select addrid from email
where emailhost = 'hotmail.com'
and
addrid in (select addrid from address
where city = 'Seattle');

 

[pranav777]

correction:


select addrid from emails
where emailhost = 'yahoo.com'
and
addrid in (select addrid from address where city = 'Seattle')
intersect
select addrid from emails
where emailhost = 'hotmail.com'
and
addrid in (select addrid from address
where city = 'Seattle');