Joining more than three tables with php and sql

[Guest]

Hi

I am familar with joining two tables but recently need to join three tables and find hard to do so.

This is my code:

$sqlconnect=mysql_query("SELECT Friends.frinum, Friends.userid, Friends.friendid, Experiences.expid, Experiences.userid,                         Experiences.title, Experiences.Dateofexpi, Experiences.description, Experiences.datetime
FROM Experiences INNER JOIN Friends ON Friends.userid=Friends.friendid
UNION

SELECT Users.id, Users.username, Users.profilepic, Friends.frinum, Friends.friendid, Friends.userid FROM Users INNER JOIN Friends ON        Friends.userid='$id'");

I have tried to incorparate union which I am not familar with and run into problems when trying to the results.

The three tables are Users, Experiences and Friends.

Can any one help.

[bregent · Accepted solution]

The select list of all queries in a union must have matching datatypes. Your union query is invalid because your fields do not match. Why do you think you need a union? A simple join is probably all you need.

[Guest]

Hi

I had click answered but not sure if using a join would work when I have three tables. I have done joins before but with two tables.

The only difference here is that I need three tables joined. This is the refined code. Could you please help..

$sqlconnect=mysql_query("SELECT Users.id, Users.username, Users.profilepic ,Friends.friendnum , Friends.userid, Friends.friendid,                
Socialfeed.sid, Socialfeed.userid, Socialfeed.title, Socialfeed.context, Socialfeed.shareoptions, Socialfeed.datetime
FROM Friends INNER JOIN Socialfeed ON Socialfeed.userid=Friends.friendid AND Friends.userid=$id");

I need Socialfeed, Users and Friends to be all joined. This result would be all the current session users friends Socialfeeds.

Thanks.

[bregent]

SQL syntax allows for an unlimited number of joins, although there are practical and physical limits determined by your DBMS, data model and data volume. Joining 3 tables is never a problem.

SELECT Users.id, Users.username, Users.profilepic, Friends.friendnum, Friends.userid, Friends.friendid, Socialfeed.sid, Socialfeed.userid, Socialfeed.title, Socialfeed.context, Socialfeed.shareoptions, Socialfeed.datetime
FROM Users INNER JOIN (Friends INNER JOIN Socialfeed ON Friends.friendid = Socialfeed.userid) ON Users.id = Friends.userid
WHERE (((Friends.userid)=$id));

Frankly, I find the old ANSI-89 joins (joins in the Where clause) to be more readable then joins in the From clause - probably because that's how I learned prior to ANSI-92.

[Guest]

Hi Bregent

Thanks so much on assisting  in join three tables. I would like to know how I can expand the above code

so that I can join four tables.

Thank you

[bregent]

Tony, as I stated before, I don't use ansi-92 syntax as I find it very confusing and verbose when joining more than a few tables. The ansi-89 syntax would look like this.

SELECT NewTable.userid, Users.id, Users.username, Users.profilepic, Friends.friendnum, Friends.userid, Friends.friendid, Socialfeed.sid, Socialfeed.userid, Socialfeed.title, Socialfeed.context, Socialfeed.shareoptions, Socialfeed.datetime

FROM NewTable, Users, Friends, Socialfeed

WHERE

Friends.friendid = Socialfeed.userid AND

Users.id = Friends.userid AND

NewTable.userid = Users.id AND

Friends.userid=$id