mysql repeated names by select

Asked

Viewed 72 times

1

I am trying to show a table where only the name of the author appears once in which his book is in stock=0, however I am getting the name of all authors in duplicate, even when the author has no stock =0. I have the table writes that has author_idautor and book_idlivro, both are foreign keys in author and book.

select nome from autor,livro where estoque=0;


 Autor
 +----------+--------------+------+-----+---------+-------+
 | Field    | Type         | Null | Key | Default | Extra |
 +----------+--------------+------+-----+---------+-------+
 | idautor  | int(11)      | NO   | PRI | NULL    |       |
 | nome     | varchar(150) | NO   |     | NULL    |       |
 | datanasc | date         | NO   |     | NULL    |       |
 +----------+--------------+------+-----+---------+-------+



 Livro
 +-----------------+--------------+------+-----+---------+-------+
 | Field           | Type         | Null | Key | Default | Extra |
 +-----------------+--------------+------+-----+---------+-------+
 | idlivro         | int(11)      | NO   | PRI | NULL    |       |
 | titulo          | varchar(100) | NO   |     | NULL    |       |
 | preco           | float        | NO   |     | NULL    |       |
 | estoque         | int(11)      | YES  |     | NULL    |       |
 | editora         | varchar(45)  | NO   |     | NULL    |       |
 | genero_idgenero | int(11)      | NO   | MUL | NULL    |       |
 +-----------------+--------------+------+-----+---------+-------+


 Escreve
 +---------------+---------+------+-----+---------+-------+
 | Field         | Type    | Null | Key | Default | Extra |
 +---------------+---------+------+-----+---------+-------+
 | autor_idautor | int(11) | NO   | PRI | NULL    |       |
 | livro_idlivro | int(11) | NO   | PRI | NULL    |       |
 +---------------+---------+------+-----+---------+-------+
mysql sql
  • 1

    can kindly by the structure of the two tables?

  • If you have two tables (author and book) you must specify some form of link between the records of each table.

  • @Jasarorion put the two tables using describe same.

  • @anonimo I have table writes that has author_idautor, book_idlivro. both are foreign keys in the author table and book respectively.

  • From what you posted above, it doesn’t seem to me that either field is a foreign key. Perhaps in the book table you have the indication of its author or even, in the general case, have another table correlating authors and books (relationship N:N).

  • after declaring the tables I did the following command, alter table writes add Foreign key (autore_author) Author (author author (author));

Show 1 more comment

3 answers

1


can perform as follows: First you create a field idautor on your table Livro

then the consultation looks like this:

SELECT 
   a.nome, l.estoque as total 
from 
   Autor a
join Escreve e on e.autor_idautor = a.idautor
join Livro l on l.idlivro = e.livro_idlivro
where
   l.estoque < 1
GROUP BY a.nome;

  • I have the table writes, not mentioned before. she has author_idautor, book_idlivro. both are foreign keys in the author table and book respectively, I could use it as reference?

  • yes can just by her on Join. da an update with this other table that you have for me update my answer.

  • I updated with the write table.

  • updated the response

0

It gave error because there is no relationship between the two tables, what I advise for example is to put a field of "idautor" in the table of books, and make the relationship on top of this field.

EX: 

select nome 
    from autor,livro
    where autor.idautor = livro.idautor
    and estoque = 0;

-1

In fact it will repeat regardless of how many books each author has, since you have not made the Join of the two tables.

After doing Join, use the word "distinct" after select, it is the simplest way to get the names without repeating.

  • sorry, but could you show an example?. I have no idea how to do this.

  • I’m on mobile now, so it’s complicated, but I’ll leave a link to a well explained article on how it works, already warning that the way your tables are, you won’t be able to, since there is no column that relates the data. https://imasters.com.br/banco-de-dados/joins-em-sql

Browser other questions tagged mysql sql

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