Skip to main content
A
Anonymous
May 2, 2011
Answered

Joining more than three tables with php and sql

  • May 2, 2011
  • 1 reply
  • 2244 views

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.

This topic has been closed for replies.
Correct answer bregent

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.

1 reply

B
bregentCorrect answer
Participating Frequently
May 2, 2011

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.

A
Anonymous
May 3, 2011

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.

B
bregent
Participating Frequently
May 3, 2011

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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices