SQL Query Joining 5 tables

Question

I need help to do a query (multiple relationships) on my Webservice + Mysql to return the result on my Android app.

I have these respective tables:

Query

I need to query a person(tb_suspect), with the attributes (Name, Cpf and Rg).

PreparedStatement statement =  con.prepareStatement("SELECT...);

The return I need in my Android app is a table in this format:

Question

Should the SQL query with SELECT and Joins be done all in a single Statement query? How to proceed? Create views would be a solution to present on Android later?

Answer 1

Just use simple Joins between the tables next time, do as @Caffe said and add your attempts and show some effort, search the site, we have excellent questions about joins as for example, the @Bacco response in this question is practically a complete article on the subject.

Undersold

SELECT
  A.descricao_artigo,
  P.dt_processo
  SP.descricao_situacao_processo
  P.pdf_Processo
FROM
  processo_judicial P
  INNER JOIN tp_situacao_processo SP on P.cd_situacao_processo = SP.cd_situacao_processo
  INNER JOIN tp_artigo A ON O.cd_artigo = A.cd_artigo
WHERE
  P.cd_processo IN (
    SELECT cdProcesso FROM suspeito_Processo WHERE cd_suspeito IN (
      SELECT cd_suspeito FROM suspeito WHERE (/*SUAS condicao de suspeito*/)
    )
  )

JOIN in all Tables

SELECT
  A.descricao_artigo,
  P.dt_processo
  SP.descricao_situacao_processo
  P.pdf_Processo
FROM
  processo_judicial P
  INNER JOIN tp_situacao_processo SP on P.cd_situacao_processo = SP.cd_situacao_processo
  INNER JOIN tp_artigo A ON O.cd_artigo = A.cd_artigo
  INNER JOIN suspeito_Processo SUP ON (SUP.cd_processo = P.cd_processo)
  INNER JOIN suspeito S ON (S.cd_suspeito = SUP.cd_suspeito)
WHERE
  WHERE /* SUAS CONDICOES DE SUSPEIRO USANDO O ALIAS S */

Using EXISTS

SELECT
  A.descricao_artigo,
  P.dt_processo
  SP.descricao_situacao_processo
  P.pdf_Processo
FROM
  processo_judicial P
  INNER JOIN tp_situacao_processo SP on P.cd_situacao_processo = SP.cd_situacao_processo
  INNER JOIN tp_artigo A ON O.cd_artigo = A.cd_artigo
WHERE
  EXISTS (
    SELECT 1 
    FROM
      suspeito_processo SUP
      INNER JOIN suspeito S ON S.cd_suspeito = SUP.cd_suspeito
    WHERE
      SUP.cd_processo = P.cd_processo AND
      /* SUAS CONDICOES DO SUSPEITO COM ALIAS S
    )

All these options are valid solutions

Answer 2

One or more consultations

If you have a specific goal of performing a query to obtain certain information organized in a certain way, yes, the most effective way is a single query to the database that returns everything already ready to use:

Consultation

Assuming you intend to consult regarding suspeito X:

SELECT
  tp_artigo.descricao_artigo AS descricao_artigo,
  processo_judicial.dt_processo AS dt_processo,
  tp_situacao_processo.descricao_situacao_processo AS descricao_situacao,
  processo_judicial.pdf_processo AS pdf_processo
FROM suspeito
INNER JOIN suspeito_processo ON (
  suspeito_processo.cd_suspeito = suspeito.cd_suspeito
)
INNER JOIN processo_judicial ON (
  processo_judicial.num_processo = suspeito_processo.num_processo
)
INNER JOIN tp_situacao_processo ON (
  tp_situacao_processo.cd_situacao_processo = processo_judicial.cd_situacao_processo
)
INNER JOIN tp_artigo ON (
  tp_artigo.cd_artigo = processo_judicial.cd_artigo
)
WHERE suspeito.cd_suspeito = 1

Outcome of the consultation

The above query performs a specific task which is the collection of the following data concerning the suspeito X:

┌──────────────────┬─────────────┬────────────────────┬──────────────┐
│ descricao_artigo │ dt_processo │ descricao_situacao │ pdf_processo │
└──────────────────┴─────────────┴────────────────────┴──────────────┘

---

Web service or Mysql View

Whether the query is for general information, ie for all suspeito, one VIEW is preferable because it becomes more practical to update in the future and also because there are no variable data to consider.

If the consultation is as I understood it, consult certain information from the suspeito X, then the web service will be the way forward because of the logic and validations to do to the data.

Note: You may have a VIEW also receiving parameters, but for that you need to create a Mysql function. Too much work and code to maintain, where it is also preferable to keep the consultation in the web service.

Answer 3

SELECT a.descricao_artigo, p.dt_processo, sp.descricao_situacao_processo, p.pdf_Processo
FROM processo_judicial AS p
INNER JOIN tp_situacao_processo as sp on p.cd_situacao_processo = sp.cd_situacao_processo
INNER JOIN tp_artigo as a ON p.cd_artigo = a.cd_artigo
INNER JOIN suspeito_processo AS susp_proc ON p.num_processo = susp_proc.num_processo
INNER JOIN suspeito AS s ON susp_proc.cd_suspeito = s.cd_suspeito
WHERE s.nome = //NOME// AND s.cpf_suspeito = //CPF// AND s. rg_suspeito = //RG//;

Answer 4

public ArrayList<ProcessoJudicial> consultar(String nome_suspeito, String cpf_suspeito, String rg_suspeito){

    ArrayList<ProcessoJudicial> results = new ArrayList<ProcessoJudicial>();

    try{

        Connection con = ConectaMySQL.obterConexao ();
        PreparedStatement statement = con.prepareStatement ("SELECT tpa.descricao_artigo, tsp.descricao_situacao_processo FROM processo_judicial AS pj INNER JOIN tp_situacao_processo as tsp on tsp.cd_situacao_processo = pj.cd_situacao_processo  INNER JOIN tp_artigo as tpa ON tpa.cd_artigo = pj.cd_artigo INNER JOIN suspeito_processo AS sproc ON sproc.num_processo = pj.num_processo INNER JOIN suspeito AS s ON s.cd_suspeito = sproc.cd_suspeito WHERE s.nome_suspeito = '"+nome_suspeito+"' AND s.cpf_suspeito = '"+cpf_suspeito+"' AND s.rg_suspeito = '"+rg_suspeito+"'");

        ResultSet result = statement.executeQuery ();

        while (result.next()){
            ProcessoJudicial obj = new ProcessoJudicial();
            obj.setDescricao_artigo(result.getString("descricao_artigo"));
            obj.setDescricao_situacao_processo(result.getString("descricao_situacao_processo"));                
            results.add(obj);
        }
        result.close();
        statement.close();
        return results;
    }
    catch ( Exception e){
        e.printStackTrace ();
    }
    return null;
}

It worked out I was able to test it in SOAPUI, just to work in my application and generate the table on Android. The body of the reply envelope was like this.

    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
    <soapenv:Body>
  <ns:consultarResponse xmlns:ns="http://wsprototipo.br.com" xmlns:ax21="http://wsprototipo.br.com/xsd">
     <ns:return xsi:type="ax21:ProcessoJudicial" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
        <ax21:descricao_artigo>Lesão Corporal</ax21:descricao_artigo>
        <ax21:descricao_situacao_processo>Transito</ax21:descricao_situacao_processo>
        <ax21:num_processo xsi:nil="true"/>
     </ns:return>
  </ns:consultarResponse>