-6

I have tree tables:

Persons:

  • id
  • name

Books

  • id
  • title

Quantity:

  • People_id
  • Product_id
  • quantity

I need a result with : in the columns the Book title, in the rows the Persons name, in the cells the quantity take from the cross of peoples and books

Andrea
  • 1
  • Joins will help. take a look at https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ which gets you the basics of inner, left, right and outer joins. We typically help those who have helped themselves. If you show what you've tried and some expected results w/ sample data I'm sure we can help. Also a [MCVE](https://stackoverflow.com/help/mcve) could help! – xQbert Aug 02 '17 at 19:13
  • This transformation is called pivoting. The linked duplicate topic demonstrates how to do both static and dynamic pivoting within mysql. Pls note that it may be more effective to perform this transformation in the application layer than in mysql. – Shadow Aug 02 '17 at 19:22
  • As an alternative, consider handling issues of data display (such as this) in application code – Strawberry Aug 02 '17 at 22:42
  • I can't resolve this data extraction with joins, I think that need a mysql prepared statements that I don't know. Now I made in a web page a table and I made one query for each cell, 100 rows and 70 columns about 7000 query and the generation of the page is very slow. – Andrea Aug 03 '17 at 20:57

2 Answers2

0
select *
from persons p join quantity q on p.id = q.people_id
join books b on q.product_id = b.id
Dennis
  • 43
  • 7
0

JOIN should help (if you need columns from all table then add alias.* for other tables in SELECT).

SELECT p.*
FROM persons p 
JOIN quantity q ON p.id = q.people_id
JOIN books b ON q.product_id = b.id
JRG
  • 4,037
  • 3
  • 23
  • 34