[eluser]Teks[/eluser]
SQL is not my forte, but I'll try to give you a hand.
Until you get more comfortable with SQL itself, I would try to steer clear off JOIN queries. You will find, that (although not as succinct) you can accomplish the same result if you just carefully build your SELECT statements, step by step. More importantly, the different database program and engines give different levels of upport for the different types of JOIN queries, so it is easier to get a consistent result across all database backends if you use the simplest form possible.
Simple SELECT statements, by themselves, are not that hard to understand. In its most basic form - which everyone can grasp - it is:
SELECT list of table fields FROM table name WHERE field operator value
*
list of table fields is generally in the format
column_name, or if more than one table is involved,
table_name.column_name. An asterix (*) can be used when you wish to include all fields of a table
*
table name should list the tables that the fields in
list of table fields belong to
*
field should be the name of one of the fields in one of the tables listed in the FROM clause
This will return a
table, containing the fields you requested, and including only the records matching the filter specified in the WHERE part of the statement.
So, let's suppose that you have a table named "Users" in your database, with the following fields:
*
id -> int, primary key
*
username -> string, not null, indexed
*
email -> string, not null, indexed
*
first_name -> string
*
last_name -> string
And let us suppose that you wanted to find a user whose id is 25:
Code:
SELECT * FROM Users WHERE id=25
If you are dealing with strings, you should enclose your value in single quotes. So, if you were looking for all user whose first name is "john", you could use:
Code:
SELECT * from Users WHERE first_name='john'
One of the most useful operators for strings is the LIKE operator, which allows you to use expressions in your search. For instance, if you wanted to find all users who had an email address in hotmail - that is, all users whose 'email' ends in "hotmail.com":
Code:
SELECT * from Users WHERE email LIKE '%hotmail.com'
In your example, however, you have 2 tables that relate to each other. Your second table, "Friends", if I understood correctly, is like this:
*
id -> int, primary key
*
user_id -> int, foreign key (= id from table Users), not null.
*
friend_id -> int, foreign key (= id from table Users), not null
*
created -> datetime
It seems to me, that a record in the "Friends" table indicates that the user shown in "user_id" has 'tagged' the user in "friend_id" as a friend. Now, let's say that you need to find all people that a user whose id is 25 has tagged as friends:
Code:
SELECT * FROM Friends WHERE user_id=25
This would give you a table containing all the fields
from the "Friends" table. What you really want, however, is the fields from the Users table - username, email, etc. You can do that, just by listing the tables and columns you want specifically, like this:
Code:
SELECT Users.username, Users.email, Friends.created FROM Users, Friends WHERE Friends.user_id=25
However, SQL has a 'smarter' way of doing this.
The 'S' in SQL stands for '
structured' - and this means that you can
structure your queries by using the results of one query as part of another. The results of a SELECT query make up a table, and you can use that table pretty much anywhere in another SELECT query. This gives us a way to nest one query inside another - and at the same time create devilishly complicated statements.
I'll show you how that works, by introducing the very important
IN operator:
Code:
Query 1 - "People User 25 Has Tagged":
SELECT * FROM Users WHERE id IN (SELECT friend_id FROM Friends WHERE user_id=25)
SQL will first evaluate the part in brackets - which is a SELECT statement, that will generate a single-column table of ids, out of the Friends table. The IN operator will then compare the id of every record in the Users table, with the entire result table. If it finds a match, then the record is included.
So, how about the opposite? How could you get a listing of all people who have tagged user 25 as
their friend?
Code:
Query 2 - "People Who Have Tagged User 25":
SELECT * FROM Users WHERE id IN (SELECT user_id FROM Friends WHERE friend_id=25)
And last of all, how about if you wanted to get a list of the people who user 25 has tagged as friends, and who have *also* reciprocated, and tagged user 25 as their friend, too?
The 2 nested select sub-queries from above, each gives us a separate list of ids. If you think about it, what we are looking for is all the ids that appear in the first list, and which also appear in the second list. We want an
intersection of both lists. We can get this intersection, like this:
Code:
Query 3 - "People Who Have Reciprocally Tagged User 25":
SELECT * FROM Users WHERE id IN (
SELECT friend_id FROM Friends WHERE user_id=25
INTERSECT
SELECT user_id FROM Friends WHERE friend_id=25)
I do not know if this is totally correct, or whether it will work for your specific database implementation, but I hope it might help steer you in the right direction.