Geek asks and answers

Help with PHP Select a query: WHERE x = y AND a = b AND IF (c! = D) Maybe?

Ok, here we go ... I have a query of choice that addresses an abstract database very much . My current query looks at 5 tables (using 6 "ANDs" to do this ... not fun) and returns all records matching all the criteria, as it should be.

My question is this: can I add something to my current query, which basically says: "If the record matches all of these , but DOES NOT match all of these."

Example:

My current request:

$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, isn't it? Well, that gives me all the records matching the criteria one (the record matches $ field_id and $ field_value).

Now I need to take all the records that this query finds, but subtract any record that meets other criteria, for example, "AND (v.event_id = e.event_id AND f.field_id = '155' AND v.value_id != '1');

This is a long and ugly version. Summarizing:

I need to create a query, 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 it possible? Can you create a query that says: "If it matches all of these AND DOES NOT match all of these?"

Let me know if someone needs more clarification ... What won’t surprise me. Thank you all.

UPDATE :: Adding an image to try to figure out what I need.

table structure http://www.everythingsirie.com/values.jpg

" " . english " " tbl_field_values ​​ ", " field_id "" 155 " _" 1 ", ."

, field_id value_id. , ...

2::

, @M42 @Michael. " "...

$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')";

, , _ value_id , field_id value_id DO. pic, field_id 155 value_id 1 0.

, _ 1, , field_id 155... AM field_id = 12 value_id = 1...

http://www.everythingsirie.com/valueid.jpg

, ... .

+3
3

, :

AND NOT (f.field_id = '155' AND v.value_id != '1')
+3

INNER JOINs, . , WHERE, IMHO... WHERE.

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'
+2

Here for update 2:

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 = '12' AND v.value_id = '1')
  OR (v.field_id = '155' AND v.value_id != '1'))
  AND v.is_latest = '1'
0
source

More articles:


All Articles