MYSQL COUNT number of matching values

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; } 
Add Comment
1 Answer(s)

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.

Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.