MySQL查询显示多个结果(MySQL query showing multiple results)

我正在构建一个搜索页面，其中表单将变量传递给SQL查询。如果我输入任何搜索参数，我得到我想要的结果，除非我将作者或主题搜索留空，如果有多个作者和/或主题与结果相关，我将得到多个结果。我的大多数记录都有多位作者或多个科目。

基本问题是，如果有一个记录有2位作者和3个相关主题，那么我得到6个结果。

我想要实现的目标：我正在尝试修改查询，以便我只收到一条记录（record_ID是唯一的），即使有多个作者或多个主题。

我遇到问题的查询中的2个JOINS是拉动作者和主题。例如，为了获得作者，我使用这个连接（主题JOIN几乎相同），变量$ fauthor从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%'

关联的表是lib_author [author_ID，author]，lib_record [record_ID，...]和record_author [record_ID，author_ID]

这是完整的查询：

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'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

最满意答案

我相信你只想按record_ID对结果进行分组。使用更简单的查询：

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

这将抓住la。*的最后一个条目。如果您想要以逗号分隔的作者列表，则可以使用group_concat（）。

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().

更多推荐