SQL query to two tables

Navigation
1

Good morning everyone!

I want to do an SQL query that retrieves all the fields from the PRODUCT table whose id field is found in the SALES table, the SALES table has the following fields: ProductID, idVenta, Observation and the table PRODUCT has idProduct, price, description .

The query I've thought about is: 

SELECT * 
   FROM 'producto' 
   WHERE 'idProducto'= 
       (SELECT idProducto 
           FROM 'ventas' 
           WHERE 'idProducto'=6)

Testing it in phpMyAdmin gives me the error that the subquery has several records, how can you make a query of this type? Is using JOIN ? Thank you very much

    
asked by midlab 23.02.2017 в 15:48
source

3 answers

4

You could make a query like this: 

SELECT * 
    FROM producto 
    JOIN ventas ON ventas.idProducto = producto.idProducto 
    WHERE producto.idProducto = 6

First, we perform the JOIN joining the table ventas with the table producto . 

JOIN ventas

Second, we indicate the union that will be made between the tables: 

ON ventas.idProducto = producto.idProducto

Third we indicate the condition: 

WHERE producto.idProducto = 6
    
answered by 23.02.2017 / 15:51
source
1
SELECT DISTINCT P.* FROM PRODUCTO P,
VENTAS V
WHERE P.IDPRODUCTO=V.IDPRODUCTO

I do not understand why you put idproduct = 6, you do not specify what you want precisely that product, but if so, then the query would be like this: 

SELECT DISTINCT P.* FROM PRODUCTO P,
VENTAS V
WHERE P.IDPRODUCTO=V.IDPRODUCTO
      AND V.ID_PRODUCTO=6
    
answered by 23.02.2017 в 16:58
1

Hello! At the beginning I had the same problem as you. But it's simple:

  

Select all columns: 

SELECT *
  

Table 1: 

FROM Producto
  

Table 2 in conjunction with table 1: 

JOIN Ventas
  

Make the union of table 1 and table 2: 

ON Ventas.CodigoProducto = Producto.CodigoProducto
  

When the product code is equal to 6: 

WHERE Producto.CodigoProducto = 6

Success! :)

    
answered by 26.10.2017 в 03:15
Function #define Error with data types in Java when compiling