complex MYSQL Query Problem

[Exerior]

I have following two tables (all fields are integers):

planets:
- id
- user_id

relations:
- id
- user_id
- other_user_id
- relation

I want select every planet in the "planets" table. "user_id" is the owner of the planet.
In addition i want the relation to the planet owner. In the "relations" table my own ID ist the "user_id" the other users has the "other_user_id". Relation can be "1" for friend or "2" for enemy.

I tried following Query:

$result = mysql_query("
SELECT a.x,a.y,a.user_id,b.relation
FROM mmobg_systems as a LEFT JOIN mmobg_relations as b ON a.user_id=b.other_user_id");

But with this query i have a problem:
I (user 1) have a good relation to user 2. But user 10 had an enemy-relation to user 2. Now the Query will select "randomly" one of those two relations.

Therefore i have added a where ($user[id] is my user ID):

$result = mysql_query("
SELECT a.x,a.y,a.user_id,b.relation
FROM mmobg_systems as a LEFT JOIN mmobg_relations as b ON a.user_id=b.other_user_id
WHERE b.user_id='$user[id]'");

Now i get only the planets with relation. But not those Planets without a relation (=neutral).

I tried to solve this Problem in one Query, because i need this Query very often.

thx for help and sorry for my bad english

[mtombs]

Don't really understand the question. It might help if the tables were called the same thing in the description and the example code...

Give an example of what you want to see and what you currently see and I will see if I can help. Also look at the UNION and UNION ALL sql commands, as I think they might help.

[Exerior]

i will try it:

The tables:

CREATE TABLE mmobg_relations (
  id int(11) NOT NULL default '0',
  user_id int(11) NOT NULL default '0',
  other_id int(11) NOT NULL default '0',
  relation int(11) NOT NULL default '0'
) TYPE=MyISAM;

INSERT INTO mmobg_relations VALUES (0, 1, 2, 5);
INSERT INTO mmobg_relations VALUES (1, 1, 1, 1);
INSERT INTO mmobg_relations VALUES (2, 10, 2, 3);



CREATE TABLE mmobg_systems (
  id int(11) NOT NULL auto_increment,
  user_id int(11) NOT NULL default '0'
) TYPE=MyISAM;

INSERT INTO mmobg_systems VALUES(1, 2);
INSERT INTO mmobg_systems VALUES(2, 1);
INSERT INTO mmobg_systems VALUES(3, 10);
INSERT INTO mmobg_systems VALUES(4, 3);

"mmobg_systems" is a table with all son-systems of the univers (there are normaly up to 10000 rows with some more fields like coordinates).
Now i want every planet => "SELECT * FROM mmobg_systems"

In addition i want the relation to the son-system owner. The owner of a system is saved as "user_id" in the "mmobg_systems" table.
In the "mmobg_relations" table are the relations between the Users. My ID is 1 and is saved in the php variable "$user[id]".
In mmobg_relations the relation (friendly or hostile) is saved through the two user-ids (user_id and other_id).
For my relation the rows with "user_id"=1 are interesting, because this rows marks the relation to the other users ("other_id").

I tried to collect the informations of the EVERY Planet and the relation to the Planet owner. If i had no relation to the planet owner, the "relation" value may be "0":

$result = mysql_query("
SELECT a.id,a.user_id,b.relation
FROM mmobg_systems as a LEFT JOIN mmobg_relations as b ON a.user_id=b.other_user_id");

But with this query i get wrong results.
If u look in the "mmobg_relations" table, you will see, that there are more then one relation to player 2 (owner of the planet with ID=1). One relation is from User 1 (me) and one Relation is from User 10 (any other User). With the query some lines above i get the wrong result/relation often, because i get the relation from User 10 to User 2, not the relation of User 1 (me) to User 2.

Therefore I tried to use a WHERE:

$result = mysql_query("
SELECT a.id,a.user_id,b.relation
FROM mmobg_systems as a LEFT JOIN mmobg_relations as b ON a.user_id=b.other_user_id
WHERE b.user_id='$user[id]'");

With this query i get a other mistake...
Now i only get the planets with relations to me. For example: I won't get the system ID=4, because i had no relation to USER 3. In this case i want to get an emty relation result.

Summary: The Result i want:
I want every Son-System of the "mmobg_systems" table and I want every relation to the system owner (saved in "mmobg_systems"->"user_id"). The relations are in the "mmobg_relations" table in the format: relation from (field)"user_id" to (field) "other_id". If there is no relation entry to the planet owner, i want to get "relation"=0 in my result.

Again: Thanks for help and sorry for my (very) bad english.

[Exerior]

An Exemple of the result (the tables and datas from the last posting).

// The Code in the php file

while($row = mysql_fetch_array($result)) {
  echo "----<br>";
  foreach($row AS $k => $v) {
     echo "$k: '$v', ";
  }
}

// The HTML-Result on the Screen (i am "user_id=1"):

-----
id=1, user_id=2, relation=5
-----
id=2, user_id=1, relation=1
-----
id=3, user_id=10, relation=0
-----
id=4, user_id=3, relation=0

// "Relation=0" means an emty value

I hope this help 🙂

[ultraslacker]

Still dont understand, but if the last is an example of what result you want then the where clause needs to allow nulls -

WHERE b.user_id=1 OR b.user_id IS NULL

[Exerior]

I think that will work ... 🙂

thx !