Ok, here we go... I have a select query accessing a very abstract database. My current query looks through 5 tables (using 6 "AND"'s to do it... not fun) and returns whatever records match all criteria, as it should.
My question is this: Can I add something to my current query that basically says "If a record matches all of these, but does NOT match all of these".
Example:
My current query:
$query = "SELECT s.state_name FROM `tbl_records` r, `tbl_states` s,
`tbl_events` e, `tbl_fields` f, `tbl_field_values` v
WHERE s.state_id = r.state_id AND f.field_id = '$field_id' AND
v.field_id = f.field_id AND v.event_id = e.event_id AND e.record_id = r.record_id
AND v.value_id = '$field_value' AND v.is_latest = '1'";
Ugly and long, right? Well that gives me all records that match one criteria (the record matches a $field_id and $field_value).
Now I need to take all the records this query finds, but subtract any record that meets another criteria, something like " AND (v.event_id = e.event_id AND f.field_id = '155' AND v.value_id != '1');
That's the long and ugly version. To Summarize:
I need to create a query that's something like:
Select "s.state_name FROM a,b,c,d WHERE (a.1 = b.2 AND c.3 = d.4 *etc*) AND ONLY IF (a.2 = b.3 AND c.4 != d.5)"
Is this possible? Can you create query that says "If matches all this AND does NOT match all this"?
Let me know if anyone needs more clarification... Which wouldn't surprise me. Thank you all.
UPDATE:: Adding an image to try and clarify what I need.
table structure http://www.everythingsirie.com/values.jpg
The two values under "need to add" need to pertain to the same record. So in plan english "For this same record, in the table 'tbl_field_values' if the record has a 'field_id' of '155' AND a field_value of '1', don't include it."
But notice there are now two field_id's and value_id's. This is what has me stumped...
UPDATE 2::
I have updated the query using answers from @M42 and @Michael. However I am still getting the same number of returns with or without the "AND NOT"...
$query = "SELECT s.state_name FROM `tbl_records` r
INNER JOIN `tbl_states` s ON s.state_id = r.state_id
INNER JOIN `tbl_events` e ON e.record_id = r.record_id
INNER JOIN `tbl_field_values` v ON v.event_id = e.event_id
INNER JOIN `tbl_fields` f ON v.field_id = f.field_id
WHERE f.field_id = '$field_id'
AND v.value_id = '$field_value'
AND v.is_latest = '1'
AND NOT (v.field_id = '155' AND v.value_id = '1')";
I think the issue is that the field_id and value_id I need to NOT match are in the same table as the field_id and value_id I DO need to match. Here is a pic showing the field_id of 155 with both a value_id of 1 and 0.
I need to NOT include records that have the value_id of 1, ONLY when the field_id is 155... But at the same time I AM trying to select records with field_id=12 and value_id=1...
table structure http://www.everythingsirie.com/valueid.jpg
I know this is a mess... Sorry.
As far as understand, you can add to your query :
AND NOT (f.field_id = '155' AND v.value_id != '1')