SQL query with duplicate items

Question

I have this situation and do not know why it happens. When I pass this SQL command the displayed items comes duplicated. What should I do to appear one item each without putting the DISTICT

SELECT                                          
  T1.*                                        
FROM                                            
  CONSULTAS T1,                               
  PARAMETROS T2                               
WHERE                                           
 (T1.DATA_CADASTRO <= T2.DATA_SISTEMA-14)

Answer 1

Suppose the T1 table is:

id     nome    data_cadastro   

1      joão    09/07/2017
2      maria   10/07/2017

and table T2 is:

id     t1_id    data_sistema   

1      1        09/07/2017
2      1        10/07/2017

When you do:

SELECT T1.*  FROM  CONSULTAS T1, PARAMETROS T2                               
WHERE (T1.DATA_CADASTRO <= T2.DATA_SISTEMA-14)

the amount of records returned is the product between the rows of the two tables. In this specific query there would be four (the correct one would be at most 2 which is the maximum number of records in each table). In this query is made the combination of the rows of the two tables involved.

To avoid the situation described above, one must relate the two tables in some way. In the example there is a relation indicated by the foreign key t1_id in table T2. Putting this relation in the query is:

SELECT T1.*  FROM  CONSULTAS T1, PARAMETROS T2                               
WHERE T1.id = T2.t1_id and (T1.DATA_CADASTRO <= T2.DATA_SISTEMA-14) 

Answer 2

By my understanding, you want to ask the following:

What are the queries that had parameters within 14 days after your registration?

If that’s the question, then the relationship is incomplete. I can’t tell which queries had parameters.

Now, if your question is:

Which queries are at least 14 days from any parameter?

I can answer with an understatement:

SELECT
    C.*
FROM
    CONSULTAS C
WHERE
    EXISTS (
         SELECT
             1
         FROM
             PARAMETROS P
         WHERE
             C.DATA_CADASTRO <= P.DATA_SISTEMA - 14
    )

Transcription of this subconsulta into Portuguese:

If there is at least one parameter created at least 14 days after a query, this query must be displayed

---

Note that I am not making a Cartesian product on FROM. With the Cartesian product as you did, for each PARAMETROS to attend to WHERE, a new line would be returned. I refer more to the Cartesian product in another answer. In your case, the projection is only of CONSULTAS, therefore appears to be the same line repeated.

How I put the table PARAMETROS as a filtering condition, not as a Cartesian product, there is no multiplication of records.