1

I am trying to filter my results to only show a person's name who is staying at a hotel in a specific city. What I have so far is

select guestName
from Guest
join Hotels on hotelNo=1

However, when I do this, the result page lists every guest name in the database, rather than the ones that are staying in hotelNo 1.

Nguyễn Văn Phong
  • 13,506
  • 17
  • 39
  • 56
howimetmyself
  • 33
  • 5
  • Does this answer your question? [SQL Server query returns too many records](https://stackoverflow.com/questions/15389678/sql-server-query-returns-too-many-records) – philipxy Mar 09 '21 at 02:57

1 Answers1

6

You should put your condition in Where clause instead.

And Join should look like this

select guestName
from Guest g
join Hotels h on g.HotelId = h.HotelId -- Primary Key of Hotels equals Foreign Key of Guest 
where hotelNo = 1

Explanation

In Join clause, You should address the condition between 2 tables (It is often between Primary key (Hotel table) and Foreign Key (Guest table))

enter image description here

More details in https://www.w3schools.com/sql/sql_join_inner.asp

Dale K
  • 25,246
  • 15
  • 42
  • 71
Nguyễn Văn Phong
  • 13,506
  • 17
  • 39
  • 56
  • 3
    Correct answer! Important thing on the join is that you make sure you first join the columns: g.HotelId = h.HotelId After that you have a summary of results that match to the join. and after that you can filter with the where statement – Andre Fritzsche Mar 02 '20 at 03:46
  • 1
    Thank you for the explanation, I was able to match the records. it was also more useful than the class textbook. – howimetmyself Mar 02 '20 at 03:59
  • @howimetmyself & Phong The problem is not on vs where but the missing condition. PS What is the legend for that diagram? How do we read it? If it's a Venn diagram, what are the kinds of sets & elements & what are the circles & labels & shading? What exactly does that one say? Especially since SQL tables are bags not sets. Those Venn diagrams are misleading & unhelpful. [CROSS JOIN vs INNER JOIN in SQL](https://stackoverflow.com/a/25957600/3404097) [Venn Diagram for Natural Join](https://stackoverflow.com/a/55642928/3404097) PS That linked site is a very poor resource. – philipxy Mar 09 '21 at 01:56
  • No, @philipxy. As you can see, the OP is using `join Hotels on hotelNo=1`. So the condition should not be placed on `Join clause` --> It should be placed on `Where` clause instead. – Nguyễn Văn Phong Mar 09 '21 at 01:59
  • No, it doesn't matter. This is very basic. Read the 1st link I gave. (Although I gave it for its section on Venn-like diagrams.) The code in the question returns too many rows because it isn't restricting to equal ids, but it should, like your code. But when there are no outer joins the conditions can be distributed between on & where in any way. – philipxy Mar 09 '21 at 02:06
  • Yes, I got you. It belongs to [`Condition within JOIN or WHERE`](https://stackoverflow.com/questions/1018952/condition-within-join-or-where) or [`placing conditions in a join query join on vs where`](https://dba.stackexchange.com/questions/95721/placing-conditions-in-a-join-query-join-on-vs-where). Personally, it should be on `Where` to follow by ASCII. Anyway, Thanks for your comment +1 – Nguyễn Văn Phong Mar 09 '21 at 02:12