Join returning many records

Asked

Viewed 444 times

1

Hi, I’ve researched here about this error and I haven’t seen one that fits my problem, so my problem is that I have two tables, I need to get some information from table B and insert in the query next to table A, for example in table A I have information of sale of item, as code, name, value, tax coupon and date of movement, in table B I have the same information but beyond all this I have information Distribution of each product, and I want a consultation result with table A information plus the Tributary information of each product of table B.

select * from itemvendageral

venda_id                             loja_id maquina_id dt_contabil             st_tipovenda contador_item funcionario_id material_id pontovenda_id nu_praca    func_cancela func_transfere nu_quantidade                           vl_preco                                func_lanca  vl_precooriginal                        st_cancelado nu_motivocanc imprimiu bn_exportou dt_lanc                 func_autorizou hr_cancelamento         bn_fechamento lote_id              status dt_alt                  bn_baixaefetuada classe_id   ticket_origem                                      observacoes                                                                                                                                                                                                                                                      vl_servico_calculado                    vl_servico_informado                    localDeEntrega                 cancelado venda_origem api_key                                            combo_id    combo_obs                                                                                                                                              indice codigo               descricao                                                                                            local_producao item_id                              versao               dispositivo_utilizado                    hr_lanc          perfilimpressao_id classe_rodizio transferido valido desconto_id desconto_estrategia                                                                                  desconto_valor                          slot_indice arredondamento                          vl_tot
------------------------------------ ------- ---------- ----------------------- ------------ ------------- -------------- ----------- ------------- ----------- ------------ -------------- --------------------------------------- --------------------------------------- ----------- --------------------------------------- ------------ ------------- -------- ----------- ----------------------- -------------- ----------------------- ------------- -------------------- ------ ----------------------- ---------------- ----------- -------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- ------------------------------ --------- ------------ -------------------------------------------------- ----------- ------------------------------------------------------------------------------------------------------------------------------------------------------ ------ -------------------- ---------------------------------------------------------------------------------------------------- -------------- ------------------------------------ -------------------- ---------------------------------------- ---------------- ------------------ -------------- ----------- ------ ----------- ---------------------------------------------------------------------------------------------------- --------------------------------------- ----------- --------------------------------------- ---------------------
9A46FAE5-AC6D-E711-8062-1C1B0DF049F3 4       5          2017-07-20 00:00:00.000 3            1             5              1000001402  0             0           NULL         NULL           1.000                                   43.00                                   5           0.00                                    N            NULL          1        NULL        2017-07-20 21:38:22.000 0              NULL                    NULL          -1                   -1     2017-07-20 21:38:27.470 0                NULL                                                                                                                                                                                                                                                                                                                            4.3000                                  4.3000                                  NULL                           0         0                                                               0                                                                                                                                                                  1      894                  FISH AND CHIPS                                                                                       1              5DFF0992-EDA7-444F-B7D0-E2B2FBD90BA8 8.5.1.472            CAIXA1                                   21:38:22.0000000 0                  0              0           1      0                                                                                                                0.00                                    0           0.00                                    43,00
9A46FAE5-AC6D-E711-8062-1C1B0DF049F3 4       5          2017-07-20 00:00:00.000 3            2             5              1000000291  0             0           NULL         NULL           2.000                                   20.90                                   5           0.00                                    N            NULL          1        NULL        2017-07-20 21:50:09.000 0              NULL                    NULL          -1                   -1     2017-07-20 21:50:47.617 0                NULL                                                                                                                                                                                                                                                                                                                            4.1800                                  4.1800                                  NULL                           0         0                                                               0                                                                                                                                                                  2      1000000288           WITTE 600ML GF                                                                                       2              5BCA5898-3919-4A66-8AB7-49351DC4906E 8.5.1.472            CAIXA1                                   21:50:09.0000000 0                  0              0           1      0                                                                                                                0.00                                    0           0.00                                    41,80
9A46FAE5-AC6D-E711-8062-1C1B0DF049F3 4       5          2017-07-20 00:00:00.000 3            3             5              1000001252  0             0           NULL         NULL           3.000                                   20.90                                   5           0.00                                    N            NULL          1        NULL        2017-07-20 21:50:27.000 0              NULL                    NULL          -1                   -1     2017-07-20 21:50:47.640 0                NULL                                                                                                                                                                                                                                                                                                                            6.2700                                  6.2700                                  NULL                           0         0                                                               0                                                                                                                                                                  3      834                  SESSION GF 600ML                                                                                     2              62859FB4-DD2D-40B8-BAEF-893C588C605E 8.5.1.472            CAIXA1                                   21:50:27.0000000 0                  0              0           1      0                                                                                                                0.00                                    0           0.00                                    62,70
9A46FAE5-AC6D-E711-8062-1C1B0DF049F3 4       5          2017-07-20 00:00:00.000 3            4             5              1000000077  0             0           NULL         NULL           1.000                                   17.90                                   5           0.00                                    N            NULL          1        NULL        2017-07-20 21:50:44.000 0              NULL                    NULL          -1                   -1     2017-07-20 21:50:47.653 0                NULL                                                                                                                                                                                                                                                                                                                            1.7900                                  1.7900                                  NULL                           0         0                                                               0                                                                                                                                                                  4      1000000075           X WALS GF                                                                                            2              2CCA95C6-6C19-4F93-B235-FE66FFB794FC 8.5.1.472            CAIXA1                                   21:50:44.0000000 0                  0              0           1      0                                                                                                                0.00                                    0           0.00                                    17,90
9A46FAE5-AC6D-E711-8062-1C1B0DF049F3 4       5          2017-07-20 00:00:00.000 3            5             5              1000001078  0             0           NULL         NULL           1.000                                   10.40                                   5           0.00                                    N            NULL          1        NULL        2017-07-20 22:00:39.000 0              NULL                    NULL          -1                   -1     2017-07-20 22:00:41.403 0                NULL                                                                                                                                                                                                                                                                                                                            1.0400                                  1.0400                                  NULL                           0         0                                                               0                                                                                                                                                                  5      6654                 WALS VERANO 600ML                                                                                    0              9B49FAEF-7F5F-405E-BC0B-457049F2E7F8 8.5.1.472            CAIXA1                                   22:00:39.0000000 0                  0              0           1      0                                                                                                                0.00                                    0           0.00                                    10,40
9A46FAE5-AC6D-E711-8062-1C1B0DF049F3 4       5          2017-07-20 00:00:00.000 3            6             5              1000001269  0             0           NULL         NULL           1.000                                   20.90                                   5           0.00                                    N            NULL          1        NULL        2017-07-20 22:11:56.000 0              NULL                    NULL          -1                   -1     2017-07-20 22:12:02.630 0                NULL                                                                                                                                                                                                                                                                                                                            2.0900                                  2.0900                                  NULL                           0         0                                                               0                                                                                                                                                                  6      849                  VERANO GF 600ML                                                                                      2              EF868F6C-AB86-4923-98FC-C92932467873 8.5.1.472            CAIXA1                                   22:11:56.0000000 0                  0              0           1      0                                                                                                                0.00                                    0           0.00                                    20,90

125,000 lines

select * from fiscal.comprovantedetalhe

comprovante_id                       ordem       codigo               descricao                      qtd                                     unidade valor                 aliquota cancelado dt_alt                  vl_unitario           ecf_serie            ecf_id      ecf_coo     ecf_ccf     ecf_datahora            base_icms             cod_ncm              prod_origem cfop       cst_icms aliq_icms_iss          cst_pis aliq_pis               cst_cofins aliq_cofins            reducao_bc_icms        dt_mov     cest    codigo_servico  item_lista_servico tipo_aliquota
------------------------------------ ----------- -------------------- ------------------------------ --------------------------------------- ------- --------------------- -------- --------- ----------------------- --------------------- -------------------- ----------- ----------- ----------- ----------------------- --------------------- -------------------- ----------- ---------- -------- ---------------------- ------- ---------------------- ---------- ---------------------- ---------------------- ---------- ------- --------------- ------------------ -------------
A7C2E9C1-AD6D-E711-8062-1C1B0DF049F3 1           10000000001025       BERLINER 300ML                 2.0000                                  UN      27,80                 T2500    0         2017-07-20 21:44:31.817 13,90                 BE091710100011203048 1           5430        2918        2017-07-20 21:44:29.000 30,58                 22030000             0           5102       00       25                     02      1,69                   02         7,69                   0                      2017-07-20 0000000                                    ICMS
A7C2E9C1-AD6D-E711-8062-1C1B0DF049F3 2           810                  FILET C FRITAS                 1.0000                                  UN      61,00                 T0840    0         2017-07-20 21:44:31.840 61,00                 BE091710100011203048 1           5430        2918        2017-07-20 21:44:29.000 67,10                 21069090             0           5101       00       8,4                    01      1,65                   01         7,6                    0                      2017-07-20 0000000                                    ICMS
81F1ADE1-AD6D-E711-8062-1C1B0DF049F3 1           822                  PALMITO PUPUNHA                1.0000                                  UN      25,00                 T0840    0         2017-07-20 21:45:25.110 25,00                 BE091710100011203048 1           5431        2919        2017-07-20 21:45:22.000 27,50                 21069090             0           5101       00       8,4                    01      1,65                   01         7,6                    0                      2017-07-20 0000000                                    ICMS
81F1ADE1-AD6D-E711-8062-1C1B0DF049F3 2           586                  WITTE 300ML                    1.0000                                  UN      10,90                 T2500    0         2017-07-20 21:45:25.110 10,90                 BE091710100011203048 1           5431        2919        2017-07-20 21:45:22.000 11,99                 22030000             0           5102       00       25                     02      1,69                   02         7,69                   0                      2017-07-20 0000000                                    ICMS
81F1ADE1-AD6D-E711-8062-1C1B0DF049F3 3           906                  DULCE LAMB                     1.0000                                  UN      25,00                 F1       0         2017-07-20 21:45:25.110 25,00                 BE091710100011203048 1           5431        2919        2017-07-20 21:45:23.000 27,50                 22072020             0           5405       60       0                      01      1,65                   01         7,6                    0                      2017-07-20                                            ICMS

75,000 lines

    select a.codigo,a.descricao,a.vl_preco,b.base_icms,b.aliq_icms_iss 
            from itemvendageral a
            left join fiscal.comprovantedetalhe as b on a.codigo=b.codigo


codigo               descricao                                                                                            vl_preco                                base_icms             aliq_icms_iss
-------------------- ---------------------------------------------------------------------------------------------------- --------------------------------------- --------------------- ----------------------
894                  FISH AND CHIPS                                                                                       43.00                                   47,30                 8,4
894                  FISH AND CHIPS                                                                                       43.00                                   43,00                 8,4
894                  FISH AND CHIPS                                                                                       43.00                                   47,30                 8,4
894                  FISH AND CHIPS                                                                                       43.00                                   43,00                 8,4
894                  FISH AND CHIPS                                                                                       43.00                                   47,30                 8,4
894                  FISH AND CHIPS                                                                                       43.00                                   43,00                 8,4
894                  FISH AND CHIPS                                                                                       43.00                                   47,30                 8,4
894                  FISH AND CHIPS                                                                                       43.00                                   47,30                 8,4
894                  FISH AND CHIPS                                                                                       43.00                                   47,30                 8,4
894                  FISH AND CHIPS                                                                                       43.00                                   47,30                 8,4

With 15 seconds of command already had 780 Thousand lines, it will generating half infinite already left for more than 2 minutes, and does not end rsrsrs

But the problem comes here, when I do a select in table A it returns me 125 thousand records, when I do a select in table B it returns me 75 Thousand records, when I do with "Join" (regardless of which it is) returns me more than 1M of records.

database sql-server query join

  • places as is your table and what has to filter

  • JOIN is a Cartesian product, so it will always multiply the lines. If you provide example data from the tables we can help you.

  • edited the post for a better purpose.

  • What are the PRIMARY KEYS of their tables?

  • itemvendageneral : venda_id(PK,FK,UNIQUEIDENTIFIER, NOT NULL) item counter_item(PK,SMALLINT,NOT NULL) tax.proofdetail : proof_id(PK,uniqueidentifier, not null) order(PK,int,not null)

  • The contador_item is equal to ordem?

  • yes. Obs: I’m learning how to use this site rsrs

  • So do like, no JOIN adds AND a.contador_item=b.ordem and tell me if it worked. I write the answer explaining

  • with the counter and order he kept the result infinite, but with and a.venda_id=b. corroborante_id worked, however the information that arrive from the tax table.comprovantedetail came NULL

  • These tables have foreign keys connecting one to another?

  • 1

    @Alexagnar, you have all the information you need for your consultation in the table. From what I know of sales systems, this table has item code, product description at the time of sale and ICSM information.

  • Was any of the answer helpful? Don’t forget to choose one and mark it so it can be used if someone has a similar question!

Show 7 more comments

2 answers

2


The problem is that the JOIN is the result of joining the rows of table A with table B. The clause ON restricts the results of this junction. Then you should build the ON of JOIN based on the foreign key columns of the tables. To determine which columns are used in the keys, run the following query:

SELECT pai.name AS pai,
       c_pai.name AS coluna_pai,
       filho.name AS filho,
       c_filho.name AS coluna_filho
  FROM sys.tables pai
       INNER JOIN sys.foreign_key_columns fk ON fk.parent_object_id = pai.object_id
       INNER JOIN sys.columns c_pai ON c_pai.object_id = fk.parent_object_id
                                   AND c_pai.column_id = fk.parent_column_id
       INNER JOIN sys.tables filho ON filho.object_id = fk.referenced_object_id
       INNER JOIN sys.columns c_filho ON c_filho.object_id = fk.referenced_object_id
                                     AND c_filho.column_id = fk.referenced_column_id
 WHERE (pai.name = 'itemvendageral'
   AND filho.name = 'comprovantedetalhe')
    OR (pai.name = 'comprovantedetalhe'
   AND filho.name = 'itemvendageral')

Rewrite his JOIN using the result columns of query previous as in the model below:

...
LEFT JOIN tabela_filha b ON b.coluna_filha_1 = a.coluna_pai_1
                        AND b.coluna_filha_2 = b.coluna_pai_2
                        -- ... Quantas colunas houverem
                        AND b.coluna_filha_z = b.coluna_pai_z
...

Using external junctions

External junctions return lines only when there is at least one row in the tables that corresponds to the junction condition. Internal joins eliminate lines that do not correspond to a row in the other table. However, external joins return all lines of at least one of the tables or displays mentioned in the clause FROM, as long as these lines meet some research criteria WHERE or HAVING. All rows are retrieved from the left table referenced with an external left junction, and all rows of the right table referenced in the external right junction. All rows of both tables are returned in a complete external merge.

The SQL Server uses the following keywords ISO for external junctions specified in a clause FROM:

  • LEFT OUTER JOIN or LEFT JOIN;

  • RIGHT OUTER JOIN or RIGHT JOIN;

  • FULL OUTER JOIN or FULL JOIN.

  • I executed the command completely and did not return any results, however looking for the only relation of the father it returns me 6 lines, having no relation to the table proof.

  • I do not believe it has a link, because when a sale is made in the system, it feeds the table itemvendageral, when it is made with a tax printer it feeds the general itemvenda and the tax.comprovantealhegeral, there are no links between them. But in the matter of getting the information I was able to solve now, I realized the existence of another table, fiscal.material which has the information of each material, but no sales link, so I can make the other problems Join. Thanks for the help @Sorack

  • About "The problem is that JOIN is a Cartesian product", who knows SQL knows that JOIN is not a Cartesian product. To obtain a Cartesian product, CROSS JOIN (SQL-92 syntax) // The use of JOIN without definition of the junction type is the same as INNER JOIN.

  • On "JOIN is the result of the multiplication of the rows of table A with table B", who knows Set Theory knows that multiplying A by B is the same thing as obtaining the Cartesian product. And, as explained earlier, who knows SQL knows that JOIN nay is a Cartesian product.

1

From what I know of sales systems, the proof-of-detail table has all the information needed for your query:

  • code - Product code
  • Description - Product at the time of sale
  • value - value is unit (price) at the time of sale
  • base_icms
  • aliq_icms_iss

If you need the latest product description and current price, you should check in the product table or some other table. I also think you need to staple them and not return the list of all.

About your Join being making a "Cartesian" product, this is because you are trying to link the tables by wrong fields. The code field is not part of the primary key between the tables, nor does it prove the uniqueness of the records alone in one of the tables.

Check the links of the tables using sp_help and you will see that there is no link constrant in the code field. This field is probably linking with the product table. If there is a link between these tables, the link will be by a composite key that you should use in your Join.

Browser other questions tagged database sql-server query join

You are not signed in. Login or sign up in order to post.