How to get the purchases of each user with 1 query [closed]

With a INNER JOIN you can do it easily:

SELECT
    Compradores.nombre,
    Compras.* 
FROM 
    Compradores
    INNER JOIN Compras ON(Compradores.id = Compras.id_comprador)
ORDER BY 
    Compradores.nombre ASC

This will show you all the buyers and their purchases made.

Try to make this query using joins.

You can learn a little in this link

select * from compradores join compras on compras.id_comprador = compradores.id where compradores.id = 1;

The tables as you have designed them now will bring you problems, one of them is the redundancy of data, which makes the size of the BD grow and makes it difficult to maintain.

For example, in the table compras the name of articulo should not be, but you must have a table dedicated to the articles.

In the database, the tables can be related by keys, which are usually numerical values that identify a single value in the other table.

Taking the same example of the shopping table, if you include the name of the article, each time you buy something you would have to write the name ... noooo, better, you put a column id_articulo that points to each item in your table.

Then the tables are joined using the JOIN statement. There are several types of JOIN right here you will find excellent answers about it.

Here is an example of a design that could be used as an orientation.

Query 1 can be modified to obtain any other result, by applying counters, filters, grouping, sorting it by any of the columns, etc.

DEMO SQL Fiddle

MySQL 5.6 Schema Setup :

CREATE TABLE compradores
    ('id_comprador' int, 'nombre' varchar(70))
;

INSERT INTO compradores
    ('id_comprador', 'nombre')
VALUES
    (1, 'Pedro'),
    (2, 'Santiago'),
    (3, 'Juan')
;

CREATE TABLE compras
    ('id_compra' int, 'id_comprador' int, 'id_articulo' int, 'cantidad' int, 'fecha' date)
;

INSERT INTO compras
    ('id_compra', 'id_comprador', 'id_articulo',  'cantidad',  'fecha')
VALUES
    (1, 1, 1, 30, '2017-05-01'),
    (2, 3, 2, 25, '2017-02-11'),
    (3, 2, 2, 90, '2017-03-01'),
    (4, 3, 1, 15, '2017-03-08'),
    (5, 1, 2, 01, '2017-04-20'),
    (6, 3, 2, 23, '2017-05-06'),
    (7, 1, 1, 40, '2017-05-03'),
    (8, 2, 1, 76, '2017-02-22')
;


CREATE TABLE articulos
    ('id_articulo' int, 'articulo' varchar(70))
;

INSERT INTO articulos
    ('id_articulo', 'articulo')
VALUES
    (1, 'Pan'),
    (2, 'Arroz'),
    (3, 'Caviar')
;

Query 1 :

SELECT
  cm.nombre, a.articulo, c.cantidad, c.fecha
FROM
  compras c  
    LEFT JOIN compradores cm ON c.id_comprador = cm.id_comprador
    LEFT JOIN articulos a ON c.id_articulo = a.id_articulo

Results :

|   nombre | articulo | cantidad |                      fecha |
|----------|----------|----------|----------------------------|
|    Pedro |      Pan |       30 |      May, 01 2017 00:00:00 |
|    Pedro |      Pan |       40 |      May, 03 2017 00:00:00 |
| Santiago |      Pan |       76 | February, 22 2017 00:00:00 |
|     Juan |      Pan |       15 |    March, 08 2017 00:00:00 |
|    Pedro |    Arroz |        1 |    April, 20 2017 00:00:00 |
| Santiago |    Arroz |       90 |    March, 01 2017 00:00:00 |
|     Juan |    Arroz |       25 | February, 11 2017 00:00:00 |
|     Juan |    Arroz |       23 |      May, 06 2017 00:00:00 |