2 replies to this topic

[gogles]

What seems to be the problem with this syntax?

With this query i am trying to select the members who have become friends with the friends of f.uid, f.uid2 is the id of the friend. Can anyone see any flaws in the logic of my query?

I am pretty sure facebook or myspace has this feature.

$query =

"SELECT * " .
"FROM relationships r INNER JOIN members m, friends f " .
"ON r.inviter = m.id " .
"WHERE f.uid2 = ANY (SELECT r.recipient) " .
"AND f.uid = '$id'";

My tables:

create table members (
id int not null primary key auto_increment,
username varchar(40) not null,
password varchar(40) not null,
name varchar(40) not null,
location varchar(40) not null,
gender char(1) not null check gender in ('m','f'),
email varchar(40) default '',
birthday date default null,
description text default '');

create table relationships (
id int not null auto_increment,
recipient int not null references members(id),
inviter int not null,
status varchar(20) not null,
epoch date not null,
primary key (id)
);

create table friends (
id int not null auto_increment,
uid int not null,
uid2 int not null,
primary key (id)
);

[WingedPanther]

The biggest problem I see is you have NO conditions on how friends f is associated with the other two tables in your join. You really need at least 2 ON conditions.

[gogles]

$query = "SELECT * " .
"FROM relationships r " .
"JOIN members m ON r.inviter = m.id " .
"JOIN friends f ON r.recipient = f.uid2 " .
"WHERE f.uid = $id";

Hmmmm still not exactly achieve its goal.