Need help with SQL JOIN

  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8415
  • Loc: USA

Post 3+ Months Ago

I'm working on a project and it requires me to join information of one table with the information of another table. I got it working, but the problem with it is that it joins everything from the user table with the other one.

I want it to join only like 3 fields instead of all of them.

PHP Code: [ Select ]
'SELECT'          => '*',
'FROM'            => FORUM_THREAD,
'RIGHT JOIN'      => USER_TABLE,
'ON'              => '`' . FORUM_THREAD . '`.`threadPosterID` = `' . USER_TABLE . '`.`userID`',
'WHERE'           => '`' . FORUM_THREAD . '`.`threadID` = ' . $tid
  1. 'SELECT'          => '*',
  2. 'FROM'            => FORUM_THREAD,
  3. 'RIGHT JOIN'      => USER_TABLE,
  4. 'ON'              => '`' . FORUM_THREAD . '`.`threadPosterID` = `' . USER_TABLE . '`.`userID`',
  5. 'WHERE'           => '`' . FORUM_THREAD . '`.`threadID` = ' . $tid


Which looks like the following once generated

SQL Code: [ Select ]
SELECT * FROM FORUM_THREAD RIGHT JOIN USER_TABLE ON `FORUM_THREAD`.`threadPosterID` = `USER_TABLE`.`userID` WHERE `FORUM_THREAD`.`threadID` = 2



Also, what's the difference between left join, right join and join?
  • demonmaestro
  • Gold Member
  • Gold Member
  • User avatar
  • Posts: 679
  • Loc: Conroe, Texas

Post 3+ Months Ago

why are you wanting to join it through one query? why not pull 2 different querys and then combine the info?
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8415
  • Loc: USA

Post 3+ Months Ago

Not sure... just was under the impression that joining it through one query saves some bandwidth and generates the page faster.

Anyway, the following SQL fixed the issue... have no idea how I thought that this was a problem lol
SQL Code: [ Select ]
SELECT FORUM_THREAD.*, USER_TABLE.userID, USER_TABLE.avatar, USER_TABLE.username, USER_TABLE.email FROM FORUM_THREAD RIGHT JOIN USER_TABLE ON `FORUM_THREAD`.`threadPosterID` = `USER_TABLE`.`userID` WHERE `FORUM_THREAD`.`threadID` = 2


Just one question remains... what's the difference between right join, left join and join?
  • demonmaestro
  • Gold Member
  • Gold Member
  • User avatar
  • Posts: 679
  • Loc: Conroe, Texas

Post 3+ Months Ago

I am not sure about the bandwidth and generating pages faster but i did find this http://www.w3schools.com/sql/sql_join.asp how ever by reading that it says that one or more tables have to have a same column inorder to work...
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8415
  • Loc: USA

Post 3+ Months Ago

I don't know... they aren't same columns that I'm joining them by, but they work. So, that theory is off the books.
  • WritingBadCode
  • Graduate
  • Graduate
  • User avatar
  • Posts: 214
  • Loc: Sweden

Post 3+ Months Ago

When you use left join OR right join there is really no "need" for a "where" statement when you use normal join use WHERE but not when you do a left or right join. Syntax could be like this for a left join:

Code: [ Select ]
SELECT people.firstname, cars.whatcar FROM people LEFT JOIN cars ON people.p_id = cars.p_id


What this code first does is declares what it tries to SELECT. in this case information from two tables: people.firstname and cars.whatcar (to be more precise the firstname from people table and the whatcar info from cars table).

Then comes the join: FROM people LEFT JOIN cars
What this does it that it selects TWO tables to work against (one being people and the other being cars). Focus are however on the left side in a left join, here you can see that PEOPLE table are on the left and CARS on the right, the difference is explained further down but note what is on the left and right.

What the next row does:

ON people.p_id = cars.p_id
Is that it sets a conditional statment to to JOINING function. The firstname where people.p_id exists will be selected no matter what. And then the conditional statement is checked to see if there is a cars.p_id and if it does that info comes along as well. But the cars.p_id could very well be empty where a people.p_id exists and basically you will get a return containing firstname but a NULL value for the second entry.

Lets imagine we got those people in our people table:

Code: [ Select ]
p_id = 1;
name = "joe";

p_id = 2;
name = "mary";

p_id = 3;
name = "quirk";
  1. p_id = 1;
  2. name = "joe";
  3. p_id = 2;
  4. name = "mary";
  5. p_id = 3;
  6. name = "quirk";

And this in our cars table:

Code: [ Select ]
p_id = 1;
name = "Peugeot 308";

p_id = 2;
name = "Mercedes E63";

p_id = 4;
name = "Saab";
  1. p_id = 1;
  2. name = "Peugeot 308";
  3. p_id = 2;
  4. name = "Mercedes E63";
  5. p_id = 4;
  6. name = "Saab";


Our select statement in the beginning would give us this:
joe owns the Peugeot 308, mary owns the Mercedes E63, quirk owns (NULL). Basically you get a return on quirk despite he don't own a car (this could be one very useful aspect of left/right joins). If you want to check out the status of all cars (who got a owner and what car has no owner) you can do one of the following:

Code: [ Select ]
SELECT people.firstname, cars.whatcar FROM people RIGHT JOIN cars ON people.p_id = cars.p_id


Focus is now on the right table and the return would be as follows:
Peugeot 308 owner joe, Mercedes E63 owner mary, Saab owner (NULL). The person quirk isn't even mentioned, focus is now on the right side (CARS).

Code: [ Select ]
SELECT people.firstname, cars.whatcar FROM cars LEFT JOIN people ON people.p_id = cars.p_id


Basically keeping it a left join and just switch place with cars and people in our join statement!

A normal join:
Code: [ Select ]
SELECT people.firstname, cars.whatcar FROM cars LEFT JOIN people WHERE (people.p_id = cars.p_id)


would return ONLY the stuff where there is a people.p_id AND a cars.p_id that matches. Basically no NULL values. This is what would returned from our JOIN example: joe owns the Peugeot 308, mary owns the Mercedes E63.

I'm not sure if this was helpful or correct at all points. I got my first JOINS working a few days ago! : P
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8415
  • Loc: USA

Post 3+ Months Ago

Awesome thanks... that helps a lot.

One more question... well a statement. I'm not using the same column names like w3schools says I should and it still works...

Post Information

  • Total Posts in this topic: 7 posts
  • Users browsing this forum: No registered users and 83 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.