what mysql join am i learning please cheers confused lol.

[redarrow]

Hi there all i am currently learning to use mysql with the defualt command prompt can you kindly tell me what join is the below and what sort of join is it cheers.

This is a made up database as i want you to tell me what kind of join am i doing cheers.

I must stress this was done here online and is not accrute just need to no what join is it, also the join should pull out all the users information form the tables useing a join but what sort is it cheers.

First make the database in the command prompt with mysql.
[code]

create database test;

[/code]

now select the database
[code]

use test;

[/code]

Copy all the database tables and past in the mysql command prompt.

[code]
create table test1(
id int not null primary key auto_increment ,
name varchar(20) not null ,
password varchar(20) not null
);

insert into test1 (name,password) values('redarrow','redarrow');

create table test2 (
id int not null primary key auto_increment ,
user_id int not null ,
email varchar(40) not null
);

insert into test2 (user_id,email) values('1','[email protected]');

create table test3 (
id int not null primary key auto_increment ,
user_id int not null ,
description text not null
);

insert into test3 (user_id,description) values('1','hello i am redarrow');

create table test4(
id int not null primary key auto_increment,
user_id int not null ,
date_added datetime not null
);

insert into test4 (user_id,date_added) values('1',now());

[/code]


now copy and past this select statement  and see the results.

[code]


SELECT test1.name, test1.password, test2.email,
test3.description, test4.date_added FROM test1,test2,
test3,test4 WHERE test1.id=test2.user_id AND
test1.id=test3.user_id AND test1.id=test4.user_id

[/code]


As you can see i want to no what sort of join is this.

i am currently enjoying mysql thank you.

cheers.

redarrow.


ps. i tested all the above and worked first time no errors must be doing somethink right haha lol

[wildteen88]

You're using a basic join.

[obsidian]

[quote author=wildteen88 link=topic=112767.msg458171#msg458171 date=1161887456]
You're using a basic join.
[/quote]

Right. You're not using a Left, Right, Inner or Outer join. I believe what that syntax for JOIN does is a FULL join (may also be an OUTER join, but I'm not sure). For other types of joins, the syntax is slightly different:
[code]
SELECT test1.name, test1.password, test2.email,
test3.description, test4.date_added
FROM test1 LEFT JOIN test2 ON test1.id = test2.user_id
LEFT JOIN test3 ON test1.id = test3.user_id
LEFT JOIN test4 ON test1.id = test4.user_id
[/code]

Then, you could apply your regular WHERE clause to the end of that.

[JustinK101]

Wikipedia a has a good article on SQL joins. Do a search on it, though it gets confusing I must admit.

[Barand]

You are using 3 INNER JOINS.

This means you must have a matching id field in ALL 4 tables, or no rows will be returned,

[redarrow]

Thank you all for your feedback it's a inner join ok cheers.

What i wont to now is the inner join method good programing code pratice.

Also is this kind of join popular.

I am asking as much as possable so i fully understand the consept of this inner join.

I have never done any joins ever before and i am currently on a mysql course.

cheers.