Join returning many records

Navigation
1

Hello, I have already researched this error and I did not see any that fit my problem, so my problem is that I have two tables, I need to get some information from table B and insert it into the query next to table A, such as in table A I have item sales information, such as code, name, value, tax coupon and date of movement, in table B I have the same information but apart from all this I have tax information for each product, and I want a query result with the information in table A plus the tax information for each product in 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 thousand 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 generate half q infinite already left for more than 2 minutes, and does not finish rsrsrs

But the problem comes from there, when I make a select in table A it returns me 125,000 records, when I make a select in table B it returns me 75 thousand records, when I do the "join" (regardless of which one ) returns me more than 1M of records.

    
asked by anonymous 29.09.2017 / 14:20

2 answers

1

As far as I know of sales systems, the proven table has all the information you need for your query:

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

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

On your join being doing a "cartesian" product, this is because you are trying to bind the tables to the 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 table bindings using sp_help and you will find that there is no binding constraint in the code field. This field is most likely linking to the product table. If there is a link between these tables, the binding will be by a compound key that you must use in your join.

    
30.09.2017 / 04:37
0

The problem is that JOIN is the result of joining the rows of table A with table B. The ON clause restricts results from this join. Then you must construct the ON of JOIN based on the columns of the foreign key 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 your JOIN using the result columns of the previous query as in the template 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 Joins

     

External joins return rows only when there is at least one row in the tables that corresponds to the join condition. The inner joins delete the rows that do not match a row from the other table. However, outer joins return all rows of at least one of the tables or views mentioned in the FROM clause, as long as those rows meet some search criteria WHERE or HAVING . All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in the right outer join. All rows of both tables are returned in a complete outer join.

     The SQL Server uses the following ISO keywords for the outer joins specified in a FROM clause:

     

  • LEFT OUTER JOIN or LEFT JOIN ;

    •   

  • RIGHT OUTER JOIN or RIGHT JOIN ;

    •   

  • FULL OUTER JOIN or FULL JOIN .

    •   
    
29.09.2017 / 15:58
How to use a JComboBox from one class to another? How to create a .bat file that the ionic serves?