Need help with an SQL Query - 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

SQL Server Development (2000)

Need help with an SQL Query

Author

Topic

firearm
Starting Member

2 Posts

Posted - 2006-11-06 : 15:03:10

I need some help writing a query that will return only rows that have a specific field in comman with each other. I can do this but was wondering if there was a better way to do it that how I plan. The way I have working would require me to create the SQL dynamically based on what the user inputs. Is there a better way. I have simplified the table and data below:

This Query shows OrganizationID's which have a common affiliationID of 1, or 4.


SELECT organization_affiliation.OrganizationID
FROM organization_affiliation
WHERE organization_affiliation.OrganizationID  IN (SELECT  organization_affiliation.OrganizationID
	FROM organization_affiliation WHERE organization_affiliation.AffiliationId=1)
AND  organization_affiliation.AffiliationId=4

Returns ABC, XYZ and CCC

This Query shows OrganizationID's which have a common affiliationID of 1, 5, or 4. Problem is the more affiliatedID criteria that is added you have to make another AND with a sub-query. Is there a better way to achieve this.



SELECT organization_affiliation.OrganizationID
FROM organization_affiliation
WHERE organization_affiliation.OrganizationID  IN (SELECT  organization_affiliation.OrganizationID
	FROM organization_affiliation WHERE organization_affiliation.AffiliationId=1)
AND  organization_affiliation.OrganizationID  IN (SELECT  organization_affiliation.OrganizationID
	FROM organization_affiliation WHERE organization_affiliation.AffiliationId=5)
AND  organization_affiliation.AffiliationId=4

Returns ABC and CCC

The organization_affiliation table and info as an example:


AffiliationId	OrganizationID
1		ABC
1		XYZ
1		XXX
1		CCC
2		ABC
3		XYZ
4		ABC
4		XYZ
4		CCC
5		AAA
5		CCC
5		ABC
6		BBB

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-06 : 15:24:56

[code]SELECT OrganizationID
FROM organization_affiliation
WHERE AffiliationID IN (1, 4, 5)
GROUP BY OrganizationID
HAVING COUNT(DISTINCT AffiliationID) = 3[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-06 : 15:29:50

Or...

DECLARE @Options TABLE (AffID INT)

INSERT	@Options
SELECT	1 UNION ALL
SELECT	4 UNION ALL
SELECT	5

SELECT		OrganizationID
FROM		organization_affiliation
WHERE		AffiliationID IN (SELECT AffID FROM @Options)
GROUP BY	OrganizationID
HAVING		COUNT(DISTINCT AffiliationID) = (SELECT COUNT(*) FROM @Options)

Peter Larsson
Helsingborg, Sweden

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-11-06 : 15:33:41

[code]SELECT organization_affiliation.OrganizationID
FROM organization_affiliation org_aff1
inner join organization_affiliation org_aff2 on org_aff1.OrganzationID = org_aff2.OrganizationID
where org_aff1.AffiliationId <> org_aff2.AffiliationId
[/code]

STAR SCHEMAS ARE NOT DATA WAREHOUSES!

firearm
Starting Member

2 Posts

Posted - 2006-11-06 : 17:26:30

quote:

Originally posted by Peso

SELECT		OrganizationID
FROM		organization_affiliation
WHERE		AffiliationID IN (1, 4, 5)
GROUP BY	OrganizationID
HAVING		COUNT(DISTINCT AffiliationID) = 3

Peter Larsson
Helsingborg, Sweden

Perfect, thanks!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources