Warm tip: This article is reproduced from stackoverflow.com, please click
mysql sql

how to show the friends of a person base on this table design, is my database design correct?

发布于 2020-03-27 10:27:50

table person & table friends - I have a table called person and friends,

  • person has column: id, and name,
  • friends has column: ctrl_no, person_id and friend_id

I want to show the name of a friend where person_id = 1, but it shows the name of the person WHERE person_id = 1,

person:

person id  |  name     
---------- | ----------
1          |  Jervy
2          |  Pamela Shane
3          |  Hikari

friends:

ctrl_no    |  person_id     |  friend_id
---------- | -------------  | -----------
1          |  1             |  2
2          |  2             |  1
3          |  1             |  3
4          |  3             |  1

I tried my query below

SELECT person.name FROM person
JOIN friends ON person.person_id = friends.person_id
WHERE person.person_id = 1;

my query shows the name of person where person_id = 1 not his friend's name...

I want show. . . WHERE person.person_id = 1

Name
Pamela Shane
Hikari

It confusing me now. T.T

Questioner
Umandap Jervy
Viewed
103
Don't Panic 2019-07-03 23:52

Your design is fine. The query just needs to be like this instead:

SELECT person.name FROM person
JOIN friends ON person.person_id = friends.friend_id
WHERE friends.person_id = 1

You want the friends' names, so you need to join the person table on friend_id. Otherwise you just get the same person's name repeatedly, which I assume is what you're seeing now.

And the criteria needs to check for friends.person_id rather than person.person_id. Since you want all the rows from that table for that person.