Help with SQL query (several records in the same field)

Navigation
0

I hope to explain myself well and that you can help me, thanks in advance, the situation is as follows:

I am developing a system that diagnoses computer failures, which will work in the following way, the user will select several symptoms and according to these symptoms the system will tell you what has (or can have) your PC, for that I have 3 tables, the table FAILS the table SYMPTOMS and one that I called SYMPTOM_FACE which is the link of the previous 2 since I manage it as a many to many relationship (a fault can have several symptoms and a symptom in turn can belong to several faults), the point is that I need to get the fault that matches the symptoms sent by the user; something like this:

  

the FALLA table has 2 fields: ID, DESCRIPTION

     

the symptoms table also has: ID, DESCRIPTION

     

the SYMPTOM-FAIL table has: ID, FAULT, SYMPTOM

Suppose that the user selects the symptoms: "the pc does not start (whose id is 2)" and "abnormal beeps are heard (id 4)",

I tried something like this: 

Select f.descripcion as falla, s.descripcion as sintoma 
from sintoma_falla sf 
inner join falla f 
     on f.id=sf.falla 
inner join sintoma s 
      on s.id=sf.sintoma 
WHERE sf.sintoma=2 and sf.sintoma=4

But it does not work, The result I want is: the name of the fault that matches all the selected symptoms.

again thanks and I hope you can help me.

    
asked by KyRa xTeC 06.11.2018 в 11:42
source

3 answers

1

The simplest thing for which you want is to check for each failure if there is a relationship with symptom_falla for both value 2 and value 4. 

SELECT f.descripcion 
FROM falla f
WHERE EXISTS (SELECT 1 
              FROM sintoma_falla sf
              WHERE sf.falla = f.id
              AND sf.sintoma = 2)
AND EXISTS (SELECT 1 
              FROM sintoma_falla sf
              WHERE sf.falla = f.id
              AND sf.sintoma = 4)
    
answered by 06.11.2018 / 11:58
source
0

Another way to preserve it is with joins counting those results that have the number of symptoms sought.

Schema (MySQL v5.7) 

CREATE TABLE FALLA (
  ID INT,
  DESCRIPCION VARCHAR(50)
)DEFAULT CHARSET=utf8;
INSERT INTO FALLA (ID, DESCRIPCION) VALUES (1, "Fallo 1");
INSERT INTO FALLA (ID, DESCRIPCION) VALUES (2, "Fallo 2");

CREATE TABLE SINTOMA  (
  ID INT,
  DESCRIPCION VARCHAR(50)
)DEFAULT CHARSET=utf8;
INSERT INTO SINTOMA (ID, DESCRIPCION) VALUES (1, "Síntoma 1");
INSERT INTO SINTOMA (ID, DESCRIPCION) VALUES (2, "Síntoma 2");

CREATE TABLE FALLASINTOMA  (
  IDF INT,
  IDS INT
);
INSERT INTO FALLASINTOMA (IDF, IDS) VALUES (1, 1);
INSERT INTO FALLASINTOMA (IDF, IDS) VALUES (1, 2);
INSERT INTO FALLASINTOMA (IDF, IDS) VALUES (2, 2);

Query # 1 

SELECT COUNT(*) AS TOTAL, F.DESCRIPCION
FROM FALLA F
INNER JOIN FALLASINTOMA FS ON FS.IDF=F.ID
LEFT JOIN SINTOMA S1 ON FS.IDS=S1.ID AND S1.ID=1
LEFT JOIN SINTOMA S2 ON FS.IDS=S2.ID AND S2.ID=2    
GROUP BY F.DESCRIPCION HAVING TOTAL=2;

| TOTAL | DESCRIPTION |

| 2 | Bug 1 |

View on DB Fiddle

    
answered by 06.11.2018 в 12:55
0

I am working on the side of Hana Studio and very often I usually run into problems of this type which is recommended in this case is a comprehensive consultation, which involves the data of (symptoms) that may have or happen.

What I highly recommend is this. 

SELECT 
F.descripcion AS "falla",
S.descripcion AS "sintoma" 
FROM sintoma_falla sf 
INNER JOIN falla F ON F.id=SF.falla 
INNER JOIN sintoma S ON S.id=SF.sintoma 
WHERE SF.sintoma IN ('2','4')

I clarify that in the same way in the WHERE you could put the other symptoms if you want a general query or leave it for several queries with different symptoms.

    
answered by 06.11.2018 в 15:54
Generate a .cert and .key certificate? Index outside the limits of the matrix - C #