Combining queries

ajvdm

Hello,

I have 2 queries that I want to combine to 1.

SELECT DISTINCT object_id
FROM field_data
WHERE data = 4
AND field_id = 4

Output: 36, 40, 41, 44

SELECT DISTINCT object_id
FROM field_data
WHERE data = 2
AND field_id = 5

Output: 38, 40, 44

I want to make 1 query with matching output, that would be in this case: 40, 44

But how? Normally I would use INTERSECT but MySQL doesn't support this.

Please help..?

Thanks.
aj

zapa

how about if you put them in a array and use array_intersect() to find the diffrence then do a select on that array

ajvdm

Zapa, can you post an example of what you mean? I don't exactly understand this construction..

zapa

sure here is a exmaple you maybe able to rework, i wrote this for you as example of what you thory would be.

<?php 

$array1 = array("a" => "green", "red", "blue"); // SQL 1 resqult simulation
$array2 = array("b" => "green", "yellow", "red"); // SQL 2 resqult simulation
$result = array_intersect($array1, $array2); // find wht same in two sql statments

// take the value of each one found and enter in DB
foreach($result as $key => $value) {  

print $value; // prints GREEN, RED
$SQL = ("INSERT into table SET field='$value'") // or whatever you sql is..
}

?>

dopey

Hi,

You maybe able to do it in one query, by joining the table onto itself e.g.

select
    f1.id
from
    field_data as f1,
    filed_data as f2
where
    f1.id = f2.id
    and f1.data = 4
    and f1.field_id = 4
    and f2.data = 2
    and f2.field_id = 5

not sure tho...