Advanced search query

[Ignis]

I have 3 tables: Organization, OrgSORT, and Membership. I have to base a query on those 3 tables where the OrgSORT.SortCodeID is either (6, 15, 51, or 52) OR the Membership.MembershipType is 2 BUT NOT the OrgSORT.SortCodeID is 50. An organization can have multiple sort codes and if any of the sort code's is 50, regardless of the other values, the organization cannot be returned in the search result.

Currently I have the SQL statement but for some reason the omit part of it does not work:

SELECT Organization.OrgID, Organization.OrgName, OrgSORT.SORTCodeID, Membership.MembershipType
FROM Organization, OrgSORT, Membership
WHERE (OrgSORT.SORTCodeID=6 OR OrgSORT.SORTCodeID=15 OR OrgSORT.SORTCodeID=51 OR OrgSORT.SORTCodeID=52 OR Membership.MembershipType=2) AND (OrgSORT.SORTCodeID<>50)
AND (Membership.OrgID=Organization.OrgID) AND (OrgSORT.OrgID=Organization.OrgID)

If someone could form a SQL query for me I would really appreciate that.

Thanks,
Arthur

 

[Ignis]

And actually, it does not limit to just those 4 SORTCodeID's.

 

[fluteplr]

SELECT Organization.OrgID, Organization.OrgName, OrgSORT.SORTCodeID, Membership.MembershipType
FROM Organization, OrgSORT, Membership
WHERE
Membership.OrgID=Organization.OrgID AND OrgSORT.OrgID=Organization.OrgID and
(OrgSORT.SORTCodeID in (6,15,51,52) OR Membership.MembershipType=2) and
not exists
(select * from orgsort os2
where os2.OrgID=Organization.OrgID and
os2.sortcodeid = 50)

 

[Ignis]

Thanks, fluteplr! The code worked well, except that the results includes multiple matches per organization with multiple SORT codes.

So, if an organization has multiple SORT codes which match the query, then multiple instances of that organization will show up in the results.

How can I get rid of these duplicates? I've tried DISTINCT but it doesn't seem to work...

 

[fluteplr]

You will have to get rid of the sortcodeid in the select part of the statement.

If you need it and you have multiple matching sort codes which one do you want to display?

 

[Ignis]

Any one.. the SORTCodeID is simply what's used to generate the search. The real results are the organization stuff (name, address, etc.) since that remains the same for every sortcodeid for that organization.

 

[fluteplr]

then just remove the sortcode from the select part of the statement and use distinct. Fields can be part of the where clause and not in the select clause