CODEWITHSUNDEEP
sql
Callum
Callum

Reputation: 725

I have 2 Tables that i'm Trying to Query with same Column

I have two tables, player and friend. I'm trying to query information from both of them to retrieve the items 

player_id (friend), 
friend_id (friend), 
nickname (player), 
FriendsNickname (Dynamic), 
relation (friend)

I created this SQL query to try to retrieve the items, but it failed on my part.

SELECT 
   friend.player_id,
   friend.friend_id,
   player.nickname, 
   (SELECT distinct player.nickname 
    FROM player, friend 
    WHERE player.player_id = friend.friend_id) as FriendName,
   friend.relation,
FROM 
   friend, player

but it returns the correct ids, nickname but not the correct friendname

"76561199766508009" "76561200657240053" "RedReaper" "CallumC"   "3" "NULL"
"76561199766508009" "76561200657240053" "CallumC"   "CallumC"   "3" "NULL"
"76561199766508009" "76561200657240053" "CrotchMan" "CallumC"   "3" "NULL"
"76561199766508009" "76561200657240053" "Callum Carmicheal" "CallumC"   "3" "NULL"
"76561199766508009" "76561200657240053" "ReconTheCat"   "CallumC"   "3" "NULL"
"76561199766508009" "76561200657240053" "redreaper" "CallumC"   "3" "NULL"
"76561200657240053" "76561199766508009" "RedReaper" "CallumC"   "3" "NULL"
"76561200657240053" "76561199766508009" "CallumC"   "CallumC"   "3" "NULL"
"76561200657240053" "76561199766508009" "CrotchMan" "CallumC"   "3" "NULL"

The SQL is supposed to return the values player id, friend id, nickname from player table, and generate the friends name using the information given (it does that but just comes back with same name every time), relation.

Upvotes: 0

Views: 47

Answers (2)

TMS
TMS

Reputation: 759

The inner join answer should be correct, but you can also use 'old style' syntax by just moving your subquery in the WHERE clause and doing an old style join there:

SELECT
   f.player_id,
   f.friend_id,
   p.nickname,
   f.nickname,
   f.relation,
FROM friend f, player p
WHERE f.friend_id = p.player_id

Upvotes: 0

Khurram Ali
Khurram Ali

Reputation: 1679

If i am understand correctly you are looking for this 

SELECT 
   f.player_id,
   f.friend_id,
   p.nickname,
   f.nickname as friendname, 
   f.relation,
FROM 
   friend f
 inner join  player p on p.player_id = f.friend_id

Upvotes: 1

Related Questions