SQL Query with Duplicate Items

1

I have this situation and I do not know why it happens. When I pass this SQL command the displayed items come duplicated. What do I do to show an item of each without by the DISTICT

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

    
asked by anonymous 10.07.2017 / 20:47

2 answers

3

By my understanding, you want to ask the following:

  

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

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

Now, if your question is:

  

Which queries are at least 14 days apart?

I can respond with a subquery:

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

Transcript of this subquery into Portuguese:

  

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

Note that I'm not making a Cartesian product in FROM . With the Cartesian product as you did, for every PARAMETROS that meets WHERE , a new row would be returned. I talk more about Cartesian product in other answer . In your case, the projection is only of CONSULTAS , so it appears to be the same line repeated.

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

    
10.07.2017 / 22:59
4

Suppose that table T1 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 query would specify four (the correct would be the maximum 2 that is the maximum amount of records in each table). In this query is made the combination of the lines of the two tables involved.

To avoid the situation described above, you 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. Placing 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) 
    
10.07.2017 / 21:15