table person & table friends - I have a table called person and friends,
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
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.