Jump to content


Photo

what mysql join am i learning please cheers confused lol.


  • Please log in to reply
5 replies to this topic

#1 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 26 October 2006 - 08:49 AM

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.

create database test;


now select the database

use test;


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

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','redarrow@redarrow.com');

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());



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



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 



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
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#2 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 26 October 2006 - 06:30 PM

You're using a basic join.

#3 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 26 October 2006 - 06:36 PM

You're using a basic join.


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

Then, you could apply your regular WHERE clause to the end of that.
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#4 JustinK101

JustinK101
  • Members
  • PipPipPip
  • Advanced Member
  • 503 posts
  • LocationSan Diego, California, US

Posted 26 October 2006 - 06:50 PM

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

#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 26 October 2006 - 07:45 PM

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,
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#6 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 27 October 2006 - 02:13 AM

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.
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users