I have, for example, the following scenario:
When I have to associate a book with a chapter I use the
cplivro table by putting the
id of the book and the
id of the chapter.
When for example I have the book with
id 1 and I want to find the capitúlos of it I do ‘two’
- First fetching all records in
equal to 1.
- Second in a for searching in the captl table an element of
resulting from the first
Is it advantageous to do so? I tried to search a little bit and I think
UNION would serve this, but I could not understand, what would it be like
As felipsmartins said:
Also, it seems unnecessary to have the table cplivro. I would move the field cplivro.idlivro to the captl table. – felipsmartins
In my view the relation Book-Chapter is a relation 1 to N, so the third table (
cplivro ) really is not necessary. It would only be if the relationship was too many for many, but I do not think that is the case. After all a chapter belongs to a book only, the same chapter is not present in more than one book.
So your table structure would look like this:
Finally, to know the chapters with your information from book 1, you would need to do:
SELECT * FROM captlt WHERE id_livro = 1;
Advantageous is not, but also not so bad. The very best would be you have your
Livro table with all the details of the book, if you go to detail the chapters of the book in another table then you will have a 1 to N relationship, in that you’d better have
id book in the
detalhe table, ie your
Capitulos table, so if you need the details of the book and you already know which book you would only need to fetch the details from the
Id of the book.
But if you can not change your table you can do this by using
Select * from cplivro L join captl c on c.idCapt = l.idCapt where l.idlivro = 1;
Only one detail that you speak, your first select returns an array, if that is it then in the Book table you have the
Ids of all the chapters, so if you that a specific chapter just filters.
Select * from cplivro L join captl c on c.idCapt = l.idCapt where l.idlivro = 1 and c.idCapt =1;