Results 1 to 5 of 5

Thread: SQL friend_id

  1. #1
    Billywizz is offline Member
    Join Date
    Jan 2011
    Posts
    57
    Rep Power
    0

    Default SQL friend_id

    hi i have a system where i can add and accept friends which insert into my database into 2 tables friend_Requests and friends.
    friend requests has 3 columns friend id Primary Key, requester Foreign_Key to username, and recipient Foreign Key to username.
    As does friends slightly different,
    friend_id INT REFERENCES friend_Requests, requester foreign key (username), and recipient foreign key (username)

    I then insert into my friends table when i receive a reciprical request ie.
    John is requester and Bill is recipient.
    Then somewhere below Bill is requester and John is recipient.
    This will insert the friend id of both these records into friends.

    My problem is that i cant seem to work out a query that selects all friend requests that have are not in friends. For example i have so far.

    SELECT friend_Requests.friend_id FROM friend_Requests, friends WHERE friend_Requests.recipient = 'John' AND friendRequests.friend_id != friends.friend_id;

    where john is the current user.

    this doesnt seem to work does anyone know what i need to do?

    Just to make it a bit clearer and to make it a bit more easy to understand say i have:
    Friend_Requests.

    friend_id Requester Recipient
    1 Kevin John
    2 Billy John
    3 John Kevin

    Friends.

    friend_id Requester Recipient
    3 John Kevin
    1 Kevin John

    The query i want is to select johns requests where he is not already friends?
    such that it should compare the friend_id's of the tables and return only '2' as friend_Requests.friend_id != friends.friend_id which is what i thought my query would do but it doesnt work it returns:
    1
    2
    2

    Any Ideas?

  2. #2
    Lawl is offline Member
    Join Date
    Mar 2011
    Posts
    4
    Rep Power
    0

    Default

    I've recently had somewhat the same problem as you Billywizz and i found out that the problem in my SELECT query the database wasn't happy with the != frasing so to speak. Try switching it with 'is not' insted :) Best idea i got, hope it'll work! :)

  3. #3
    Billywizz is offline Member
    Join Date
    Jan 2011
    Posts
    57
    Rep Power
    0

    Default

    Hi Lawl Thanks for your suggestion, Unfortunately it made no difference i also looked up other ways of using "is not equal to" such as "<>" but this did not work either :/

  4. #4
    Billywizz is offline Member
    Join Date
    Jan 2011
    Posts
    57
    Rep Power
    0

    Default

    It seems to work fine when i do it the other way round and try to find where friend_id in both tables match but its when i change the sign to 'not equal' that it all goes wrong :/

  5. #5
    Billywizz is offline Member
    Join Date
    Jan 2011
    Posts
    57
    Rep Power
    0

    Default

    Solved it you have to use NOT IN (then your table and column of the thing you want to get compare against eg. friends.friend_id )
    :)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •