Triple Join isn't working

[lightnb]

I'm trying to do my first many-to-many join, but I guess I'm not getting the syntax right. I read through an article on how to do it, and used the same syntax, but It's producing an error:

the query is:
SELECT * FROM Users JOIN Entity-Users ON Users.users_KEY = Entity-Users.users_ID JOIN Entity ON Entity-Users.entity_ID = Entity.entity_KEY WHERE handle='lightnb'

and my lovely "Verbose Death" function says:

I've Failed You:
# Script Name: /RVM/interface/join_entity.php
# Include File: /home/rahl/public_html/RVM/include/functions.php
# errorno=1064
# error=You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-Users ON Users.users_KEY = Entity-Users.users_ID JOIN Entity ON Entity-User' at line 1
# query= SELECT * FROM Users JOIN Entity-Users ON Users.users_KEY = Entity-Users.users_ID JOIN Entity ON Entity-Users.entity_ID = Entity.entity_KEY WHERE handle='lightnb'

Array
(
[0] => Array
(
[file] => /home/rahl/public_html/RVM/interface/join_entity.php
[line] => 23
[function] => safe_query
[args] => Array
(
[0] => SELECT * FROM Users JOIN Entity-Users
ON Users.users_KEY = Entity-Users.users_ID JOIN Entity
ON Entity-Users.entity_ID = Entity.entity_KEY
WHERE handle='lightnb'
)

)

)


and the PHP script:

<?PHP

session_start();

// Include the shared registration functions
Require('../include/functions.php');
Require('../include/SQL_connect.php');



if($_SESSION['loggedin'] == 'true')
{
// , Entity-Users WHERE Users.users_KEY=Entity-Users.users_ID

$query = "SELECT * FROM Users JOIN Entity-Users
ON Users.users_KEY = Entity-Users.users_ID JOIN Entity
ON Entity-Users.entity_ID = Entity.entity_KEY
WHERE handle='".$_SESSION['user_name']."'";



// echo $query;
$result = safe_query($query);
$num_rows = mysql_num_rows($result);

if($num_rows == 0)
{
echo "There are no entities currently assigned to you.";
}
elseif($num_rows == 1)
{
$row = mysql_fetch_array($result);




echo "You are curently assigned to". $row['entity_name'];
}
elseif($num_rows > 1)
{
echo "There is more than one entity currently assigned to you.";
}
}
else // if the user is not logged in
{
echo "your not logged in!";
}
?>


Thank you in advance,

Nick

[NightShift58]

Try backquoting this: Entity-Users

[lightnb]

Which is the backquote?

I used:

$query = "SELECT * FROM Users JOIN `Entity-Users`
ON Users.users_KEY = Entity-Users.users_ID JOIN Entity
ON Entity-Users.entity_ID = Entity.entity_KEY
WHERE handle='".$_SESSION['user_name']."'";


and got:

error=Unknown column 'Entity' in 'on clause'

[Nanscombe]

Hi Lightnb,

You could try it as:

$query = "SELECT * FROM Users JOIN `Entity-Users` EU
ON Users.users_KEY = EU.users_ID JOIN Entity
ON EU.entity_ID = Entity.entity_KEY
WHERE handle='".$_SESSION['user_name']."'";

MySQL doesn't appear to like having a - (hyphen) in the middle of a table name, Field Names are probably the same. I tend to stick to using _ (underscore) instead.

Using the backquote ` as a delimiter around the tablename makes life easier but I've suggested using an alias of EU to make referencing the table, in the rest of the query, easier.

Regards
Nigel

[lightnb]

Thank you, that fixed the issue. I will use underscores for table names so as to avoid complications in the future.

Out of curiosity, can you create a circular join back to the first table?

For example:

User 1 belongs to Entity 1 whose contact person is User 2.

I can return the forign key for the contact user, but can I get the 'handle' or 'first name' from the users table without a second query?

[Nanscombe]

Hi Lightnb,

Yes, you could do a circular join. You would just use two aliases, something like:

Select E.Name EntityName, U1.Name User, U2.Name Contact FROM Entity E, Users U1, Users U2 WHERE U1.ID = E.User_ID AND U2.ID= E.Contact_ID

This is over-simplified because it would need matches on ALL tables, you would need to use Outer (Right?) Joins between the tables for it to work properly.

I really must try and get out of the habit of using the AS keyword in SQL.

Regards
Nigel

[lightnb]

So you call the users table again using a different alias, like a second instance of it?

[Nanscombe]

Yes.

That's correct. Just re-use it as you wish.

But I would really find out about using Outer (Right) joins in MySQL.

This allows you to see ALL of the records in the Main table (Entity) and any records that match in the Secondary (User) rather than seeing only records that match in both tables.

Entity table:
Entity_ID, User_ID, Contact_ID
1,1,2

User table:
User_ID, handle
1, Nigel

Using an ordinary Join between the tables would show no records because there would be no match to the Contact_ID

Entity_ID, User.Handle, Contact.Handle
No records returned

Using Outer (Right) Joins:

Entity_ID, User.Handle, Contact.Handle
1, Nigel, Null

Regards
Nigel

[Nanscombe]

Hi,

I've just been looking and LEFT, RIGHT and Outer joins and the SQL below should give you an example of what you can do.

Select E.Entity_Name, User.handle Users_Name, Contact.handle Contact_Name From Entity E LEFT JOIN Users Contact on (E.Contact_ID=Contact.Users_ID) LEFT JOIN `Entity-Users` EU on(E.Entity_ID=EU.Entity_ID) LEFT JOIN Users User on(EU.Users_ID=User.Users_ID);

That would deal with missing links between the tables as well.

Nigel

[Nanscombe]

*** Deleted duplicate post ***