How to fix Sql query

0
votes

Please i have been trying to get a query to return the friends a user has,in my members table, i have user id ,firstname and lastname of the member, i have user id column and friend id column in the friends table, now i am combining my members table with friends table so i can get the name of a friends.

users table

user_id firstname lastname

   2      John        drake         
   3       Hamer      Joy       
   4        Finter     Richy

friends table

friends_id user_id friend_id

   1           2          3         
   2            4         2         
   3            4         3

here is the query i executed 

SELECT a.friends_id,a.user_id, 
       a.friend_id, b.firstname, b.lastname
FROM friends AS a,users As b
WHERE (a.friend_id = b.user_id OR a.user_id = b.user_id) AND
      (a.friend_id = 2 OR a.user_id =2)

here is the result i am getting 

friends_id  user_id  friend_id  firstname   lastname    
   1           2       3          John      drake       
   1           2       3          Hamer     Joy         
   3           4       2          John      drake       
   3           4       2           Finter    Richy

This is the result i am expecting

friends_id  user_id  friend_id  firstname   lastname    
   1           2       3          Hamer     Joy         
   3           4       2           Finter    Richy
2
mysqlsql
Can you supply the contents of your users and friends tables? Also, there is an error in your query, you have an extra comma between b.lastname and the FROM keyword - this query can not run as supplied due to syntax errors. - GMc
Thanks i just updated the question, i have fixed the syntax error - d0uph1x

2 Answers

0
votes

perhaps take a union of the specified individual's friends plus the users who have him as a friend:

select a.friends_id, a.user_id, a.friend_id, b.firstname, b.lastname
from friends a, users b
where a.user_id = 2 and b.user_id = a.friend_id 

union

select a.friends_id, a.user_id, a.friend_id, b.firstname, b.lastname
from friends a, users b
where a.friend_id = 2 and b.user_id = a.user_id

This is all a single query resulting from the union of 2 inner queries.

0
votes

I think the problem with your original query is that you are using an OR condition in your joins. Normally OR's in join conditions are not helpful.

Here is another solution that is a bit shorter and uses ANSI join syntax so you can clearly see the projections. Disclaimer, I haven't tested it in a database, so there may be an error. However, you can also access the original user name as well if you like (I've added that as the last two columns).

select F.friends_id, U.user_id, FR.user_id,
    FR.firstname, FR.lastname,
    U.firstname, U.lastname
FROM users U join Friends F on
        U.user_id = F.user_id
    JOIN Users FR on
        F.friend_id = FR.user_id
where U.user_id = 2 or FR.user_id = 2
;

As a general rule, using the ANSI join syntax makes your query much easier to read and helps to ensure all of the join conditions are specified. It also gives you easy access to more join types.