Do not repeat records in a join

Question

Do not repeat records in a join

Navigation

#1 by (2 votes)
#2 by (2 votes)
#3 by (1 votes)

3

I have two tables: One with 1009 records and another with 11949. When I do a join, I have 11949 records, and the records in the 1009 records table are repeated several times. I need to do a select with join, but bring only the amount of records contained in the smaller table, since everything in the smaller table exists in the largest. Below my select:

select er.id_exm_rea 
from t_cmo_Exame_Realizado er 
inner join t_cmo_planilha_leitura pl on er.id_xfc = pl.id_xfc

t_cmo_read_lead - > Smaller table (1009 records)

t_cmo_Exame_Realizado - > Table Major (11949 records)

sql sql-server sql-server-2014

asked by anonymous 20.04.2017 / 22:19

3 answers

When changing any line of PHP, it gives 500 error (server) How to insert a breakpoint in Javascript by code

score 2 · Answer 1

A simple distinct will solve:

SELECT DISTINCT ER.ID_EXM_REA FROM T_CMO_EXAME_REALIZADO ER     
INNER JOIN T_CMO_PLANILHA_LEITURA PL 
  ON ER.ID_XFC = PL.ID_XF

The DISTINCT keyword deletes duplicate rows from the results of a select statement. If you do not specify distinct , all rows will be returned (including duplicates).

In your case, if you select all id_exm_rea in t_cmo_Exame_Realizado without DISTINCT , 11949 rows will be returned.

score 2 · Answer 2

2

Try this:

SELECT er.id_exm_rea
FROM t_cmo_Exame_Realizado er 
WHERE er.id_exm_rea IN (
    SELECT pl.id_xfc FROM t_cmo_planilha_leitura pl
)

20.04.2017 / 22:41

score 1 · Answer 3

You get more records than the quantity in the lowest table because of the following axiom (which I am deducing):

For each element present in the smaller table, there are N elements in the larger table, where N ranges from zero to infinity.

That is, your data mass for your work has the minimum size dictated by the smaller table size, and maximum dictated by the larger table size.

Any reduction in the mass of data you make to make your search the smallest table size will likely result in data dumping.

So if you want to ensure that the search will not have more records than the smaller table, you should:

Consider only the smallest table in the search;
Discard larger table data according to some criteria.

The distinct clause eliminates the reps in the final search result. According to comments in Ismael's response, you have tried this, and yet your case has not been resolved. That way, I believe your goal will not be achieved with just the least amount of records in the table. I think you need to review the requirement you are meeting.