I am have a heck of time getting a handle on this left join s when there are 3 or more tables involved. For testing porposes I have create a temp table.
It looks like this:
CREATE TABLE job_asn (
jid int(11) DEFAULT '0' NOT NULL,
uid int(11) DEFAULT '0' NOT NULL
);
# Dumping data for table 'job_asn'
INSERT INTO job_asn VALUES ( '1', '2');
INSERT INTO job_asn VALUES ( '4', '3');
INSERT INTO job_asn VALUES ( '2', '1');
CREATE TABLE job_number (
jid int(11) NOT NULL auto_increment,
descr varchar(255),
UNIQUE jid (jid)
);
# Dumping data for table 'job_number'
INSERT INTO job_number VALUES ( '1', 'test 1');
INSERT INTO job_number VALUES ( '2', 'test 2');
INSERT INTO job_number VALUES ( '3', 'test 3');
INSERT INTO job_number VALUES ( '4', 'test 4');
INSERT INTO job_number VALUES ( '5', 'test 5');
# Table structure for table 'user'
CREATE TABLE user (
uid int(11) NOT NULL auto_increment,
user varchar(255) NOT NULL,
PRIMARY KEY (uid)
);
# Dumping data for table 'user'
INSERT INTO user VALUES ( '1', 'bill');
INSERT INTO user VALUES ( '2', 'tom');
INSERT INTO user VALUES ( '3', 'dick');
INSERT INTO user VALUES ( '4', 'harry');
#######################
I have tried a couple of things
First
SELECT * FROM job_number LEFT JOIN job_asn ON job_asn.jid = job_number.jid
this is the results I am looking for but with user from the user table
jid descr jid uid
1 test 1 1 2
2 test 2 2 1
3 test 3
4 test 4 4 3
5 test 5
I then tried a multple left join and recived way to much data.
SELECT * FROM job_number LEFT JOIN job_asn ON job_asn.jid = job_number.jid,
user LEFT JOIN job_asn AS j_asn ON user.uid = j_asn.uid
jid descr jid uid uid user jid uid
1 test 1 1 2 1 bill 2 1
2 test 2 2 1 1 bill 2 1
3 test 3 1 bill 2 1
4 test 4 4 3 1 bill 2 1
5 test 5 1 bill 2 1
1 test 1 1 2 2 tom 1 2
2 test 2 2 1 2 tom 1 2
3 test 3 2 tom 1 2
4 test 4 4 3 2 tom 1 2
5 test 5 2 tom 1 2
1 test 1 1 2 3 dick 4 3
2 test 2 2 1 3 dick 4 3
3 test 3 3 dick 4 3
4 test 4 4 3 3 dick 4 3
5 test 5 3 dick 4 3
1 test 1 1 2 4 harry
2 test 2 2 1 4 harry
3 test 3 4 harry
4 test 4 4 3 4 harry
5 test 5 4 harry
Any thoughts?
Thank you
It looks like this:
CREATE TABLE job_asn (
jid int(11) DEFAULT '0' NOT NULL,
uid int(11) DEFAULT '0' NOT NULL
);
# Dumping data for table 'job_asn'
INSERT INTO job_asn VALUES ( '1', '2');
INSERT INTO job_asn VALUES ( '4', '3');
INSERT INTO job_asn VALUES ( '2', '1');
CREATE TABLE job_number (
jid int(11) NOT NULL auto_increment,
descr varchar(255),
UNIQUE jid (jid)
);
# Dumping data for table 'job_number'
INSERT INTO job_number VALUES ( '1', 'test 1');
INSERT INTO job_number VALUES ( '2', 'test 2');
INSERT INTO job_number VALUES ( '3', 'test 3');
INSERT INTO job_number VALUES ( '4', 'test 4');
INSERT INTO job_number VALUES ( '5', 'test 5');
# Table structure for table 'user'
CREATE TABLE user (
uid int(11) NOT NULL auto_increment,
user varchar(255) NOT NULL,
PRIMARY KEY (uid)
);
# Dumping data for table 'user'
INSERT INTO user VALUES ( '1', 'bill');
INSERT INTO user VALUES ( '2', 'tom');
INSERT INTO user VALUES ( '3', 'dick');
INSERT INTO user VALUES ( '4', 'harry');
#######################
I have tried a couple of things
First
SELECT * FROM job_number LEFT JOIN job_asn ON job_asn.jid = job_number.jid
this is the results I am looking for but with user from the user table
jid descr jid uid
1 test 1 1 2
2 test 2 2 1
3 test 3
4 test 4 4 3
5 test 5
I then tried a multple left join and recived way to much data.
SELECT * FROM job_number LEFT JOIN job_asn ON job_asn.jid = job_number.jid,
user LEFT JOIN job_asn AS j_asn ON user.uid = j_asn.uid
jid descr jid uid uid user jid uid
1 test 1 1 2 1 bill 2 1
2 test 2 2 1 1 bill 2 1
3 test 3 1 bill 2 1
4 test 4 4 3 1 bill 2 1
5 test 5 1 bill 2 1
1 test 1 1 2 2 tom 1 2
2 test 2 2 1 2 tom 1 2
3 test 3 2 tom 1 2
4 test 4 4 3 2 tom 1 2
5 test 5 2 tom 1 2
1 test 1 1 2 3 dick 4 3
2 test 2 2 1 3 dick 4 3
3 test 3 3 dick 4 3
4 test 4 4 3 3 dick 4 3
5 test 5 3 dick 4 3
1 test 1 1 2 4 harry
2 test 2 2 1 4 harry
3 test 3 4 harry
4 test 4 4 3 4 harry
5 test 5 4 harry
Any thoughts?
Thank you