How to limit SQL resultset output to 1 result instead of every queried result?

Question

I am working on a web app that contains a search function that returns a restaurant when the user submits a query that matches that restaurant.

For example, if the user enters 'Pizza' and enters 'Whiskey', I want the result to output the matching restaurant ONCE.

Instead, this exact query results in the restaurant name appearing multiple times.

Tables:

Foodtbl Drinkstbl Restaurantstbl

SQL Code:

" SELECT r.restname, r.type, r.location, r.website, r.reviews, r.PageLink"
                                    + " FROM restaurants r, food f, drinks d"
                                    + " WHERE SOUNDEX(f.foodcategory) = SOUNDEX(?)"
                                    + " AND SOUNDEX(d.drinkvariety) = SOUNDEX(?)"
                                    + " AND r.restid = f.restid"
                                    + " AND r.restid = d.restid");

While Loop result:

<% while (restResults.next()) { %>
                                    <td><%= restResults.getString("restname") %></td>
                                    <td><%= restResults.getString("type") %></td>
                                    <td><%= restResults.getString("location") %></td>
                                    <td><a href="<%= restResults.getString("website") %>"</a>Go to Website</td> 
                                    <td><a href="<%= restResults.getString("reviews") %>"</a>Go to Reviews</td>
                                    <td><a href ="<%= restResults.getString("PageLink") %>"</a>More Info</td>

Is this a problem with my SQL statement or is it a Loop problem?

Thank you.

*Never* use commas in the `FROM` clause. *Always* use proper, explicit, **standard** `JOIN` syntax. — Gordon Linoff, Feb 20 '19 at 12:20
The loop can't "see" any data that's not in the query result nor does it seem to display anything other than what's in the result, so that leaves...(hint: it's **not** the loop) — Kevin Anderson, Feb 20 '19 at 12:33

LukStorms · Answer 1 · 2019-02-20T13:09:03.377

0

Since you're only selecting fields from restaurants, and don't use aggregate functions, you could add a DISTINCT.
So that the returned records will be unique.

Also, it's best to use the more recent JOIN syntax instead of the outdated comma syntax.

" SELECT DISTINCT r.restname, r.type, r.location, r.website, r.reviews, r.PageLink"
  + " FROM restaurants r"
  + " JOIN food f ON f.restid = r.restid"
  + " JOIN drinks d ON d.restid = r.restid"
  + " WHERE SOUNDEX(f.foodcategory) = SOUNDEX(?)"
  + "   AND SOUNDEX(d.drinkvariety) = SOUNDEX(?)");

edited Feb 20 '19 at 13:09

answered Feb 20 '19 at 12:31

LukStorms

28,916
5
31
45

Thank you, this worked. I tried the DISTINCT word first time round, but must have misspelled it or something as it didn't work but is working now. I will look at including this new syntax, thanks for the heads up – Philivey123 Feb 20 '19 at 12:38
@Philivey123 To get you started, [here](https://stackoverflow.com/q/38549/4003419) is an old popular SO post about the types of joins. INNER JOIN is the default, so the INNER can be ommited. And sadly, MySql doesn't have FULL JOIN. – LukStorms Feb 20 '19 at 13:03

How to limit SQL resultset output to 1 result instead of every queried result?

1 Answers1