MySQL – Query Returns Same Value Multiple Times

MySQLquery

I have following command :

select * 
from Books  
JOIN Keys ON Books.Bnumber = Keys.Bnumber 
where ( 
    Books.Author1 like '%,%' and 
    Books.Author1 like '%,John%' or (
        Books.Author2 like '%,John%' and 
        Books.Author2 like '%,%'
    )
) and 
    Name1 like '%' and 
    Keys.Keyword like '%' 
order by NAME1 ;

The query returns the same record multiple times.

The repeating parts in query (Books.Author1 like '%'…) are needed, for my application requires it. ( I think it should have nothing to do with my problem)

What could cause this?

Best Answer

The query returns the same record multiple times.

Variants:

  1. one of source tables (or even both) contains duplicates;
  2. the records are NOT duplicates, there exists at least one field (in a record pair which seems to be duplicates for you) where values differs.

the table Keys contains multiple equal values of Bnumber, but that is required because 1 Book has multiple keywords.

It is variant 2.

You make a mistake while thinking that the records are duplicates. They differs - at least in Keyword field taken from Keys table.

You may "compress" the output obtaining one book per recodset by grouping and concatenating all keys into one field with keys list separated by comma:

SELECT Books.id, 
       Books.Bnumber, 
    /* another fields from Books table */
       GROUP_CONCAT(Keys.Keyword) Keys_list
FROM Books  
JOIN Keys ON Books.Bnumber = Keys.Bnumber
/* all WHERE conditions which you need */
GROUP BY Books.id, 
         Books.Bnumber, 
      /* another fields from Books table */
Related Question