Complex join - 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)

Complex join

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-07-07 : 05:35:02

Richard writes "I am trying to create a join on two tables which is giving me headaches.

I have two tables as follows:

[tblSite]
site_id (unique id for site)
site_name
...

[tblServiceSite]
ssid (unique id for service)
site_id
service_name
email
...

The tblSite table is a list of agencies, the tblServiceSite is a table of services provided by these agencies. There can be many site_id's in the tblServiceSite table relating nback to the tblSite table. The email field is sometimes null.

What I want to return with my query is a list like the following"
tblSite.site_id, tblSite.site_name, email_exists

Basically I want to list the tblSite table but showing a field (email_exists) that indicates whether any email address exists in the tblServiceSite table for the particular site_id. I have tried a number of queries but can't quite figure this out."

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-07-07 : 05:53:11

There's a few different ways of doing this. Here's one:

SELECT
	s.site_id, 
	s.site_name, 
	MAX(CASE 
			WHEN ss.site_id IS NULL 
			THEN 0 
			ELSE 1 
		END) AS email_exists 
FROM
	dbo.tblSite AS s
	LEFT JOIN dbo.tblServiceSite AS ss
		ON s.site_id = ss.site_id
			AND ss.email IS NOT NULL
GROUP BY
	s.site_id, 
	s.site_name

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources