Query in two tables at the same time

I have, for example, the following scenario:

    
        
            Table
        
        
             Column01
        
        
             Column02
        
        
             Column03
        
    Home
     
        
            book
        
        
            id
        
        
            name
        
        
            genre
        
    Home

        
            captl
        
        
            id
        
        
            name
        
        
            content
        
    
Home

        
            cplivro
        
        
           id
        
        
            idcap
        
        
            id book
        
    

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’ SELECT :

• First fetching all records in cplivro where idlivro is
  equal to 1.
• Second in a for searching in the captl table an element of array
  resulting from the first SELECT

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 UNION ?

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:

    
        
            Table
        
        
             Column01
        
        
             Column02
        
        
             Column03
        
        
             Column04
        
    Home
     
        
            book
        
        
            id
        
        
            name
        
        
            genre
        
        
            
        
    Home

        
            captl
        
        
            id
        
        
            name
        
        
            content
        
        
            book_id
        
    

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 JOIN

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;