duplicate records of a query

Question

duplicate records of a query

Navigation

#1 by (1 votes)

0

I'm making a query to find all those products that are not related to a table called Stock where the field Ruta within the table Stock is different from id I'm going through the url, that means searching for all those products that are not associated with Stock , where the path of said Stock equals the parameter of the url.

I'm doing it this way:

@productos = Producto.joins('left outer join stock on productos.Clave=stock.Articulo')
                     .select('productos.*,stock.Articulo')
                     .where('stock.ruta != ? AND productos.Status = ?', params[:id], "A")

I have a small problem and I am looking for those records that are not associated with Stock where the field Ruta is different from the id I am passing as a parameter. When the product is not associated with Stock and I create a Stock associated with the product, it does not bring the query, and it is just what I want, but if the product is already associated with another Stock , so I have it associated to the Stock with the Ruta id of the parameter, it keeps bringing it because another Stock with another Ruta id has it.

How could I solve it?

The structure of the tables are: a "Product" has many "Stock" and a "Stock" belongs to a "Product"

Product: Password, Product, Status

Stock: IdStock, Article, Route

ruby-on-rails ruby

asked by LuisC 18.11.2016 в 16:27

source

1 answer

Retrieve the focus at the beginning of the TextField if it loses it in Java Can MySQL format AUTO_INCREMENT?

score 1 · Accepted Answer

We must first search all the articles that have the route that we do not want, and we exclude from the rest of the articles, which are the ones we are going to obtain.

create temp table mcprod (clave varchar(10), producto text, status char(1));
create temp table mcstock (id int, articulo varchar(10), ruta int);
insert into mcprod values ('0001', 'Producto 1', 'A');
insert into mcprod values ('0002', 'Producto 2', 'A');
insert into mcprod values ('0003', 'Producto 3', 'N');
insert into mcstock values (1, '0001', 1);
insert into mcstock values (2, '0001', 2);
insert into mcstock values (3, '0001', 3);
insert into mcstock values (4, '0002', 1);
insert into mcstock values (5, '0002', 2);
insert into mcstock values (6, '0002', 5);


select mcprod.clave, mcprod.producto 
from mcprod
where mcprod.status = 'A' 
and mcprod.clave not in (select distinct articulo from mcstock where ruta=3); 

| clave |  articulo  |
|-------|------------|
|  0002 | Producto 2 |
|-------|------------|

link