CODEWITHSUNDEEP
sql
Rob Sanderson
Rob Sanderson

Reputation: 21

SQL trouble with different tables

First ever question on here, apologies if I am being dense. I have a user table and a friendslink table and want to select * from the user table where the user id = Here is my SQL:

SELECT * 
FROM user
INNER JOIN FriendsLink 
ON user.usr_ID=FriendsLink.ub_lnkID1 
ON user.usr_ID=FriendsLink.ub_lnkID2
WHERE usr_ID =" .  $_SESSION['usr_ID']

I may be even more dense than I thought - that or perhaps I did not phrase my question properly... the query is required to return all details pertaining to any user that appears in either ub_lnkID1 OR ub_lnkID2 with the user with the session ID appearing in the corresponding ub_lnkID field.

Upvotes: 0

Views: 38

Answers (2)

Damien Black
Damien Black

Reputation: 5647

instead of two 'on' stantements you need an 'and' or and 'or' depending on what you want.

SELECT * 
FROM user
INNER JOIN FriendsLink 
ON user.usr_ID=FriendsLink.ub_lnkID1 
  OR user.usr_ID=FriendsLink.ub_lnkID2
WHERE usr_ID =" .  $_SESSION['usr_ID'] .""

But are you sure about your logic? user.usr_ID will be equal to FriendsLink.ub_lnkID1 and FriendsLink.ub_lnkID2 at the same time? Maybe you are looking for an 'or'...

The way sql works is that each 'join' statement has only one 'on', however, you can use 'and' and 'or' statements to make that 'on' as complicated as you need.

EDIT:

Ok, to get the user information of the friends, it is probably best to do something like this:

"SELECT user.* 
FROM user
JOIN FriendsLink
  ON user.usr_ID=FriendsLink.ub_lnkID1 
  OR user.usr_ID=FriendsLink.ub_lnkID2
WHERE FriendsLink.ub_lnkID1 = ".$_SESSION['usr_ID'] ."
  OR FriendsLink.ub_lnkID2 = ".$_SESSION['usr_ID'] ."
GROUP BY user.usr_ID"

Added the group by so that you don't get the same friend twice. You just needed to change the where to select when the user is a friend.

Upvotes: 0

M.Ali
M.Ali

Reputation: 69554

SELECT * 
FROM user INNER JOIN FriendsLink 
ON user.usr_ID=FriendsLink.ub_lnkID1 
AND user.usr_ID=FriendsLink.ub_lnkID2  --<-- This can be `AND` or `OR` depending  
WHERE usr_ID =  --<-- Your value          -- on you requirement

Upvotes: 1

Related Questions