Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to force column to be in array if it matches the whole thing?

I have the following MySQL query:

SELECT tbPacientesComunicacao.id 
FROM tbPacientesComunicacao 
WHERE tbPacientesComunicacao.idRefUser = 1 
   AND tbPacientesComunicacao.idRefPaciente = 4
   AND tbPacientesComunicacao.id IN (
     SELECT idRefComunicacao 
     FROM tbPacientesComunicacaoDestinatarios 
     WHERE idRefUser IN (2,8)
   );

The issue is that MySQL returns the ID if either 2 or 8 matches… how can I say hey only return the id if both numbers (2 and 8) matches?

Table tbPacientesComunicacao

id description
1 Something

Table tbPacientesComunicacaoDestinatarios

idRefComunicacao UserID
1 5
1 7

When a new record is being inserted, I have to check if that record doesn’t match any of the communications already in the database. So I pass the user IDs in an array(5,8) then I have to check it somehow if there is something in the database the matches EXACTLY this combination of UserIDs, in this test it shouldn’t match because I have 5 and 7 not 5 and 8 but using "in" it says it was matched because it found at least one of them… how can I determine that only matches if all numbers in the array matches the table userID?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I found a solution that I was trying to avoid but since using a MySQL query seems to be really hard... this works using PHP and MySQL:

    $total = count($_POST["responsaveis"]);
                $rsCheck = $this->conn()->prepare("SELECT tbPacientesComunicacao.id, count(tbPacientesComunicacaoDestinatarios.idRefUser) as total FROM `tbPacientesComunicacao` inner join tbPacientesComunicacaoDestinatarios on tbPacientesComunicacao.id = tbPacientesComunicacaoDestinatarios.idRefComunicacao where tbPacientesComunicacao.idRefPaciente = :idRefPaciente and tbPacientesComunicacao.idRefUser = :idRefUser group by tbPacientesComunicacao.id having total = :total order by tbPacientesComunicacao.id");
                $rsCheck->bindValue(":idRefPaciente", $_POST["paciente"], PDO::PARAM_INT);
                $rsCheck->bindValue(":idRefUser", $_SESSION["usuarioID"], PDO::PARAM_INT);
                $rsCheck->bindValue(":total", $total, PDO::PARAM_INT);
                $rsCheck->execute();

                while($ar = $rsCheck->fetch(PDO::FETCH_OBJ)){
                    $rs = $this->conn()->prepare("SELECT idRefUser FROM `tbPacientesComunicacaoDestinatarios` where idRefComunicacao = :id");
                    $rs->bindValue(":id", $ar->id, PDO::PARAM_INT);
                    $rs->execute();

                    $destinatarios = array();
                    while($arUser = $rs->fetch(PDO::FETCH_OBJ)){
                        $destinatarios[] = $arUser->idRefUser;
                    }
                    sort($destinatarios);
                    sort($_POST["responsaveis"]);               
                    if($destinatarios == $_POST["responsaveis"]){
                        echo "they are the same";
                    }
                }

  2. 0

    I think you can do something like:

    SELECT tbPacientesComunicacao.id 
FROM tbPacientesComunicacao 
WHERE tbPacientesComunicacao.idRefUser = 1 
   AND tbPacientesComunicacao.idRefPaciente = 4 
   AND tbPacientesComunicacao.id IN (
       SELECT idRefComunicacao 
       FROM tbPacientesComunicacaoDestinatarios 
       WHERE idRefUser IN (2,8) 
       GROUP BY idRefComunicacao HAVING COUNT(*) = 2
   );

    This way you only select the idRefComunicacao for which idRefUser IN (2,8) and there are exactly two results (the one with idRefUser 2 and the one with 8)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search