Select distinct from 2 tables - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Select distinct from 2 tables

Author

Topic

tricky231
Starting Member

2 Posts

Posted - 2006-04-05 : 03:22:15

I have 2 tables as below:

tblSite

-----------------------
| Site_id | Site_name |
-----------------------
| 23 | test site |
-----------------------
| 24 | demo site |
-----------------------
| 25 | site3 |
-----------------------

etc...

tblServiceSite

---------------------------------------------------------
| Site_id | Service_id | Service_name | EmailAddr |
---------------------------------------------------------
| 23 | 123 | service1 | bill@bloggs.com |
---------------------------------------------------------
| 23 | 124 | service2 | |
---------------------------------------------------------
| 24 | 125 | service3 | |
---------------------------------------------------------
| 24 | 126 | service4 | mary@jones.com |
---------------------------------------------------------

etc...

What I am trying to do is obtain a list of unique site names,
but at the same time highlight if that site name has a service
(could be one or many) that has an email address.
Eg.
test site (has email)
demo site (has email)
site3 (no email)

This is what I have at present, but it produces duplicates of a
site name for ones with a service that have an email address.

SELECT DISTINCT Site_id,Site_name,
CASE
WHEN EmailAddr <> '' THEN '(has email)'
ELSE '(no email)'
END
FROM tblSite
JOIN tblServiceSite ON tblSite.Site_id = tblServiceSite.Site_id

Results will appear like this:
test site (has email)
test site (no email)
demo site (has email)
demo site (no email)
site3 (no email)

Any ideas anyone??

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-05 : 04:11:26

Use left outer join

Madhivanan

Failing to plan is Planning to fail

tricky231
Starting Member

2 Posts

Posted - 2006-04-05 : 07:25:38

Just to let you all know I did sort this out, sql statement below:

SELECT DISTINCT tblSite.Site_id,
Site_name = CASE
WHEN ss1.Referral_email <> '' THEN Site_name + '**'
ELSE Site_name
END
FROM tblSite
LEFT OUTER JOIN tblServiceSite ss1 ON tblSite.Site_id = ss1.Site_id AND EmailAddr <> ''
ORDER BY tblSite.Site_name

This will append a '**' to each of the site_names that have a service that has an EmailAddr

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-05 : 08:59:05

Try this also

SELECT DISTINCT tblSite.Site_id,
Site_name = Coalesce(ss1.Referral_email,Site_name, Site_name + '**')
FROM tblSite
LEFT OUTER JOIN tblServiceSite ss1 ON tblSite.Site_id = ss1.Site_id AND EmailAddr <> ''
ORDER BY tblSite.Site_name

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources