Query in two tables at the same time

Asked

Viewed 272 times

4

I have for example the following scenario:

Table Coluna01 Coluna02 Coluna03
book      id               name        gender
captl     id               name        content
cplivro  id               idcap         idlivro


When I have to associate a book with a chapter I use the table cplivro 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 his capitals I make 'two' SELECT:

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

Is it advantageous to do so? I tried to research a little and I think the UNION I could use that, but I couldn’t understand, what it would be like with UNION?

mysql sql

  • What you really want is to make a JOIN. I think your query would look something like this: SELECT 
 livro.*, cplivro.*, captl.*
FROM
 livro
 INNER JOIN
 cplivro ON (cplivro.idlivro = livro.id AND cplivro.idcap = captl.id);

  • 1

    Also, it seems to me unnecessary the existence of the table cplivro. I would move the field cplivro.idlivro to the table captl.

  • Guy the best is the JOIN, you can join the two tables by the common keys, in case the book id

2 answers

1

Advantageous is not, but also not so bad. The best would be you have your table Livrowith all the details of the book, if you will detail the chapters of the book in another table then you will have a relationship 1 to N, in that you better have the id of the book in the table detalhe, ie your table Capitulos, this way if you need the details of the book and already know which is the book you would only need to search the details by Id of the book.

But if you can’t change your table you can be making use JOIN

Select * from cplivro L
join captl c
on c.idCapt = l.idCapt
where l.idlivro = 1;

Only one detail you talk about, your first select returns an array, if that’s so in the Book table you have the Ids of all chapters, so if you have a specific chapter just filter.

Select * from cplivro L
join captl c
on c.idCapt = l.idCapt
where l.idlivro = 1
and c.idCapt =1;

1

As the felipsmartins said:

Also, it seems unnecessary to me the existence of the cplivro table. I would move the cplivro.idlivro field to the captl table. - felipsmartins

In my view the Book-Chapter ratio is a ratio 1 to N, hence the third table (cplivro) really not necessary. It would only be if the relationship were too many for many, but I don’t think that’s the case. After all a chapter belongs to one book only, the same chapter is not present in more than one book.

So your table structure would look like this:

Table Coluna01 Coluna02 Coluna03 Column04
book      id               name        gender        
captl     id               name        content   id_book


Finally, to know the chapters with your information from book 1, just do:

SELECT * FROM captlt WHERE id_livro = 1;

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.