Need help with SQL JOIN

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.



Which looks like the following once generated




Also, what's the difference between left join, right join and join?

why are you wanting to join it through one query? why not pull 2 different querys and then combine the info?

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


Just one question remains... what's the difference between right join, left join and join?

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...

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.

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:



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:


And this in our cars table:



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:



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).



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

A normal join:


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

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...