MySQL query showing multiple results

Question

I'm building a search page where a form will pass variables to a SQL query. If I input any search parameter, I get the results I want, unless I leave either the author or the subject search blank, I will get multiple results if there are multiple authors and/or subjects associated with the results. Most of my records have multiple authors or multiple subjects.

The basic problem is that if there is one record with 2 authors and 3 associated subjects, then I get 6 results.

WHAT I AM TRYING TO ACHIEVE: I am trying to modify the query so that I only receive one record (record_ID is unique), even if there are multiple authors or multiple subjects.

The 2 JOINS in the query I am having problems with are to pull the author and subject. For example, for to get the author, I use this join (the subject JOIN is almost identical) and the variable $fauthor is passed in from php:

SELECT la.author, la.author_ID, ra.record_ID
FROM lib_author AS la
JOIN record_author AS ra ON la.author_ID = ra.author_ID
JOIN lib_record AS lr ON ra.record_ID = lr.record_ID
WHERE la.author LIKE '%$fauthor%'

The associated tables are lib_author [author_ID, author], lib_record [record_ID, ...], and record_author [record_ID, author_ID]

Here is the complete query:

select a.record_ID, a.year, n.title, y.author, y.author_ID, j.journal, pox.publisher, lox.language, a.volume, a.issue, a.pages, a.abstract, s.subject
   FROM lib_record AS a  
   JOIN lib_title as n on n.title_ID = a.title_ID
   JOIN (SELECT la.author, la.author_ID, ra.record_ID
       FROM lib_author AS la
       JOIN record_author AS ra ON la.author_ID = ra.author_ID
       JOIN lib_record AS lr ON ra.record_ID = lr.record_ID
            WHERE la.author LIKE '%$fauthor%') AS y 
   JOIN (SELECT ls.subject, ls.subject_ID, rs.record_ID 
        FROM lib_subject AS ls
        JOIN record_subject AS rs ON ls.subject_ID = rs.subject_ID
        JOIN lib_record AS lr ON rs.record_ID = lr.record_ID
            WHERE ls.subject LIKE '%$fsubject%') AS s
   JOIN lib_journal AS j on j.journal_ID = a.journal_ID
   JOIN lib_publisher pox ON a.publisher_ID = pox.publisher_ID
   JOIN lib_language lox ON a.language_ID = lox.language_ID
   WHERE y.record_ID = a.record_ID 
   AND s.record_ID = a.record_ID
   AND n.title LIKE '%$ftitle%'
   AND j.journal LIKE '%$fjournal%'

I hope someone has some insight. Thanks

Answer 1

I believe you just want to group the results by record_ID. Using your simpler query:

SELECT la.author, la.author_ID, ra.record_ID
  FROM lib_author AS la
  JOIN record_author AS ra ON la.author_ID = ra.author_ID
  JOIN lib_record AS lr ON ra.record_ID = lr.record_ID
  WHERE la.author LIKE '%$fauthor%'
  GROUP BY ra.record_ID

this will grab the last entry for la.*. If you want a comma-separated list of authors, you could use group_concat().