MYSQL COUNT number of matching values

Question

I have a database with 2 columns. character_id and killmail_id The object is to get a list of all killmail_ids of a specific character_id then to get all character_ids that have a matching killmail_ids and count how many times those character_ids appear

So far I have gotten this: ($target is the character_id to be searched for)

SELECT `character_id`, COUNT(`killmail_id`) FROM `attackers` WHERE $target NOT IN (SELECT `killmail_id` FROM `attackers` WHERE `character_id` <> $target) AND `character_id` <> $target  GROUP BY `killmail_id` ;

This almost works, but it counts the total number of killmail_ids a character has I only want it to count the killmail_id if the the $target also has a matching killmail_id

if that doesn't make sense, this is what I was using before (PHP) and I'm trying to eliminate the looping by having a more specific SQL statement if possible.

function seenWith($target){
    $sql = "SELECT `killmail_id` FROM `attackers` WHERE character_id='" . $target . "';";
    $mySql = $db->prepare($sql);
    $mySql->execute();
    $results = $mySql->fetchAll();
    $friends = array();
    foreach ($results as $idx => $array){
        foreach ($array as $key => $value){
            $sql = "SELECT `character_id`,`killmail_id` FROM `attackers` WHERE killmail_id='$value' AND character_id<> '".$target."';";
            $mySql = $db->prepare($sql);
            $mySql->execute();
            $rData = $mySql->fetchAll();
            foreach($rData as $index => $friendName){
                array_push($friends, htmlspecialchars($friendName['character_id'], ENT_QUOTES));
            }
        }
    }
    return $friends;
}

update your question add a proper data sample and the expected result — ScaisEdge, Jul 11 '20 at 07:34
Hi, you should give the sql insert scripts and create scripts, which when ran in database creates the database objects. — JagaSrik, Jul 11 '20 at 07:50
Tried to add data sample, failed. Don't know what I'm doing (my first post here) — The Coder, Jul 11 '20 at 07:51
check this https://stackoverflow.com/questions/3978326/get-insert-statement-for-existing-row-in-mysql/37351876 — JagaSrik, Jul 11 '20 at 07:55
try Export/Import option in mysql workbench, pleaase modify the col name and data if its sensitive. — JagaSrik, Jul 11 '20 at 07:57
See about sql injection and the importance of prepared and bound queries. And also see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Jul 11 '20 at 08:02

score 0 · Accepted Answer · answered Jul 11 '20 at 09:14

One option uses exists:

select character_id, killmail_id
from attackers a
where
    character_id <> :target
    and exists (
        select 1
        from attackers a1
        where 
            a1.character_id = :target
            and a1.killmail_id = a.killmail_id
    )

:target represents your input argument, which you should be passing as a query parameter rather than by concatenating it in the query string.

MYSQL COUNT number of matching values

1 Answers1