Query in two tables at the same time

Question

Query in two tables at the same time

Navigation

#1 by (1 votes)
#2 by (1 votes)

4

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 ?

mysql sql

asked by anonymous 18.02.2016 / 23:09

2 answers

Timthumb has security exploit issues? Create add-on that associates number with an image

score 1 · Answer 1

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;

score 1 · Answer 2

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;