Hey All,

To start: sorry for the crappy title. I have a query that deals with some legacy tables and returns results that I don't want it to. I know it is the query but I am also curious if it is possible to do this the way I want.

Tables (stripped out unnecessary fields):
Code:
CREATE TABLE `partner_campuses` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `campus` varchar(150) DEFAULT '',
  `capped` enum('n','y') NOT NULL DEFAULT 'n',
  `paused` enum('n','y') NOT NULL DEFAULT 'n',
  `active` enum('n','y') NOT NULL DEFAULT 'y',
  `deleted` enum('n','y') NOT NULL DEFAULT 'n',
  PRIMARY KEY (`id`),
) ENGINE=MyISAM

CREATE TABLE `partner_states_zips` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `partner_campus_id` int(10) unsigned NOT NULL DEFAULT '0',
  `state_zip` varchar(6) DEFAULT '',
  `field_type` enum('all','zip','state') NOT NULL DEFAULT 'zip',
  `allow` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
) ENGINE=MyISAM

CREATE TABLE `zip_codes` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `zip_code` varchar(5) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `state_prefix` varchar(2) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=MyISAM
The query below works great in the following situations:
1. An entry in the partner_states_zips is set to: 1, 100, '', 'all', 1 -> this allows all combination of states and zips (ie no restrictions)
2. If there are restrictions, the above entry should not be used, as the query below allows all, and in the table would be entries like:
2, 101, '10000', 'zip', 1
3, 101, '10001', 'zip', 1
In this case only the first two zips would be allowed, nothing else would.
3. It gets interesting when you want to not allow a state or a zip but allow all others. Because of the query below and/or the structure of the table (which is not easy to change at this point because of everything known and unknown written of top of it) if there are exceptions, then everything else has to be included as allowed. Example: if you want to deny Minnesota (4, 102, 'MN', 'state', 0), then all other states would need to be listed as allowed (49 other entries). This becomes a pain when working w/ zips: if 500 are not allowed, the other 42K zips have to entered into the table, creating a huge amount of rows in the table.

A few new people started, not knowing this, and logically did the following:
5, 103, '10000', 'zip', 0
6, 103, '10001', 'zip', 0
7, 103, '10002', 'zip', 0
8, 103, '', 'all', 1
Their logic is allow all but deny the listed zips. Much shorter amount of information to store but doesn't allow the query below to work - it needs all other zips to be listed and marked allowed to filter out the 3 (in this example) that are not allowed (the current way the system works).

Query:
Code:
SELECT
	DISTINCT(pc.id)
	, pc.campus
FROM 
	partner_campuses pc
LEFT JOIN
	partner_states_zips psz ON psz.partner_campus_id = pc.id
LEFT JOIN
	zip_codes zc ON zc.state_prefix = psz.state_zip
WHERE
	pc.partner_id = 6
	AND 
	(
		psz.state_zip = "01109"
		OR zc.zip_code = "01109"
		OR psz.field_type = "all"
	)
	AND pc.capped = "n"
	AND pc.active = "y"
	AND pc.paused = "n"
	AND pc.deleted = "n"
I have fiddled with the query but can't come up w/ a solution that allows the above logic to correctly filter out. I would really like to use that method but realize if table structure doesn't allow.

Sorry for the long post. Hopefully I have been detailed enough. Let me know if you need anything else to help.

Thank you for your time and help,
Oach