This is a touch more advanced that what I'm used to, but once
I get to grips with it I'll be fine.
This is what I ACTUALLY have in the dbase
The members are stored in a table called USERS
USERS TABLE -->
USER_UID --> Primary key and unique UID for the user.
other stuff, USER_FIRSTNAME, USER_LASTNAME etc
Then I have the FRIENDS TABLE
FRIEND_UID --> Unique Primary key to identify each record
USER_UID --> The ID of the USER that we are referring to
(same value as USER_UID in table USERS
FRIEND_USER_UID --> The ID of the friend of the user we
are referrring to, also their USER_UID in table USERS
As suggested I can set up a relationship between USERS and
FRIENDS so that both the USER_UID and FRIEND_USER_UID in the
FRIENDS table have foreign keys linked to the same field USER_UID
in the table USERS
I think all of the above is correct as far as the dbase set
up is concerned.. Oh and I also indexed all of the 3 fields in
FIELDS, not sure if that is such a good idea?
When I tried all of this with the relationship in place it
would not let me manually insert a legit record for a user, say
user_UID 13, and I wanted to have friend user_UID=52, it gave an
error.
This set up means that I guess if I have user 13 and user 52
as friends that I'll have two records right?
FRIENDS TABLE
user_uid,friend_user_uid
13,52
52,13
Now it's the SQL query I come unstuck on, I could use basic
SQL to just
SELECT friend_user_UID
FROM FRIENDS
WHERE user_uid = 13
to get all friends of 13, but with the relationship in place
I am presuming this is bad and not efficient, and I need to put
that inner join in place for the USER table so that performs
better. This is the bit where I'm a tad stuck :(