How to avoid one query's match id with another query

Question

My first query got all related ids from Table A and another query got all necessary ids from Table B.

Now I want to make 3rd query by those ids which not match above both query.

Example:

Query return: 1 , 5, 10, 15.
Query return: 5.
Query want to make by: 1, 10, 15.

Here is my work:

//1st query:
foreach((array)$f2_ids as $indx => $value) {
    $g = mysqli_query($dbh,"SELECT id FROM update WHERE 
    `to_id`='".$pg_ids[$indx]."' AND `from_id`='".$f2_ids[$indx]."' GROUP BY id") or die(mysqli_error($dbh));
    while ($rows = mysqli_fetch_assoc($g)) {
    $ids[] = $rows['id'];
    }
}

//2st query:
foreach((array)$ids as $id) {
    $p = mysqli_query($dbh,"SELECT post_id FROM view WHERE `post_id`='".$id."' AND `user_id` ='".$session->id."'") or die(mysqli_error($dbh));
    while ($rows = mysqli_fetch_assoc($p)) {
    $Vids[] = $rows['post_id'];
    }
}

//3st query: I tried which not get proper result.
foreach((array)$ids as $index => $value) {
    if($ids[$index] !== $Vids[$index]){  // avoid match ids
    //echo $ids[$index];
    $j = mysqli_query($dbh,"SELECT * FROM update WHERE `id`='".$ids[$index]."' ORDER BY created DESC");
    }
}

UPDATE:

foreach((array)$ids as $id) {
    $p = mysqli_query($dbh,"SELECT * 
    FROM update 
    WHERE `id` = '$id'
    AND `id` NOT IN (SELECT post_id
            FROM view
            WHERE `post_id`='$id'
            AND `user_id`='$myid')
    ORDER BY created DESC") or die(mysqli_error($dbh));
            $rows = mysqli_fetch_assoc($p);
            $id = $rows['id'];
            echo $id;
}

So your 3rd query should consist of ID's `not returned` by those above 2 queries ? — Akshay, Aug 08 '15 at 08:39

JiFus · Accepted Answer · 2015-08-08T10:35:23.490

1

Check out subqueries

I think it should be something like this:

SELECT * 
FROM update 
WHERE `id` NOT IN (SELECT id 
                  FROM update 
                  WHERE `to_id`=".$pg_ids[$indx]."
                  AND `from_id`=".$f2_ids[$indx]."
                  GROUP BY id )
AND `id` NOT IN (SELECT post_id
                FROM view
                WHERE `post_id`=".$id."
                AND `user_id`=".$session->id.")
ORDER BY created DESC

edited Aug 08 '15 at 10:35

answered Aug 08 '15 at 09:11

JiFus

959
7
19

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near IN (SELECT id FROM update WHERE `to_id` – koc Aug 08 '15 at 10:22
Look, I don't know exactly how your tables look. But the point is, that in the 2 subqueries you select the ID's that shouldn't match in the 3rd query. What does query does is: 1. Select all ID's from update (subquery 1), 2. Select all ID's from view (subquery 2), 3. Select all updates from update (complete query) where the id field is not equal to the id's you selected in the first 2 queries – JiFus Aug 08 '15 at 10:26
Your query field is ok as my table, and understand also your query but cannot remove that error. Thank you – koc Aug 08 '15 at 10:32
Try my query again, I made a flaw. I guess it will work now – JiFus Aug 08 '15 at 10:35
Please try the query with the expected values for `$pg_ids[$index]`, `$f2_ids[$index]`, `$ids[$index]` and `$session->id` in phpmyadmin or what you use. Could you give me the exact error? – JiFus Aug 08 '15 at 11:20

score 0 · Answer 2 · answered Aug 08 '15 at 08:04

0

I think you should use a separate 3rd query don't take values from 1st and 2nd query..

answered Aug 08 '15 at 08:04

Ausaf Sharif

51
2
12

1

I was trying but I cant did it. – koc Aug 08 '15 at 08:06
Maybe look at how others have asked questions under the MySQL tag – Strawberry Aug 08 '15 at 09:03

How to avoid one query's match id with another query

2 Answers2