I am doing a query to a database but the result it gives me does not make sense. According to the DB there are 5 different books of which there are a total of 7 copies, of book 1,3 and 5 --> 1 copy of book 2 and 4 there are 2 of each. From loan we have that book 1 and 4 has been lent 2 times, book 2 has been lent 3 times and 5 and 3 have been lent 1 time. The statement of the query is this
`A partir de dicha base de datos, se pretende crear un informe con iReport donde se reflejen por cada título los préstamos realizados de cada ejemplar incluyendo su edición. Además, por cada título se obtendrá el total de préstamos de los ejemplares que existan para dicho título.`
My query is
Select l.titulo, e.edicion, count(p.idLibro)
from Libros l, Ejemplares e, Prestamos p
where l.id = e.idLibro = p.idLibro
however the result it throws is:
titulo edicion count(p.idLibro)
Los pilares de la tierra 2010 14
Doesn't make sense if we match the id should all the books come out not just 1 besides where does it count 14? If book 1 has only been borrowed twice, the DB is in this link https://drive.google.com/open?id=1lj0AZFDS4UajTLebL4IwBFw5GspOZl1q
I don't know what database you use, but according to the standard, the query should be
If the primary/foreign keys specified in the
INNER JOIN
are correct, it should give you the number grouped by title and edition.EDIT If you want to limit any parameter you must go to
WHERE
, if what you want is to limit the aggregation (in this case theCount
) you need theHAVING