Bruce, I'm getting the four rows with one correctly matching the userID for tom and the others returning NULLs for the user info.
Lachlan -----Original Message----- From: bruce [mailto:[EMAIL PROTECTED] Sent: Tuesday, 10 August 2004 5:13 PM To: 'Lachlan Mulcahy' Subject: RE: left join issues!!! but what's your output... when i run the select query you provided, i get a single row... as opposed to the 4/four rows that i expect... (or at least the 4 rows that i'm trying to get!!!) i think what's happening is that the query is triggering off the only value in the userTBL, and matching that with the match value in the parsefileTBL which results in only a single row/match.. what i want/what i'm trying to get is the names of the user in place of the userID (and NULL) if no user exists... so what's the result you're getting?? and i may have a data issue... if you're getting the 4 rows, then i'll send the db/tabls i have to you so you can take a quick look.... it might be something simple that i'm just missing! thanks -bruce -----Original Message----- From: Lachlan Mulcahy [mailto:[EMAIL PROTECTED] Sent: Monday, August 09, 2004 11:17 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: left join issues!!! Bruce, I have reconstructed the database you have given me and used the following query successfully (the one I originally gave you). I think there is something wrong with your data. SELECT p2.statusID as parseStatus, p2.action as parseAction, u2.name, p1.userID, u3.ID, u3.url as schoolUrl, u3.urltype as urlType, u1.name as school, u1.city as city, u1.stateVAL as state FROM universityTBL as u1 LEFT JOIN university_urlTBL as u3 ON u1.ID = u3.universityID LEFT JOIN parsefileTBL as p1 ON u3.ID = p1.university_urlID LEFT JOIN parsefilestatusTBL as p2 ON p1.fileID = p2.fileID LEFT JOIN userTBL as u2 ON p1.userID = u2.ID WHERE u1.ID = '40'; To follow is the mysqldump of the test DB I used.. Hope this helps you out, Regards, Lachlan -- MySQL dump 9.11 -- -- Host: localhost Database: mysql_testing -- ------------------------------------------------------ -- Server version 4.0.20-Max-log -- -- Table structure for table `parsefileTBL` -- CREATE TABLE parsefileTBL ( university_urlID int(5) NOT NULL default '0', filelocation varchar(50) NOT NULL default '', name varchar(50) NOT NULL default '', userID int(10) NOT NULL default '0', fileID int(10) NOT NULL auto_increment, PRIMARY KEY (fileID), UNIQUE KEY university_urlID (university_urlID,filelocation) ) TYPE=BerkeleyDB; -- -- Dumping data for table `parsefileTBL` -- INSERT INTO parsefileTBL VALUES (157,'','',0,1); INSERT INTO parsefileTBL VALUES (158,'','',0,2); INSERT INTO parsefileTBL VALUES (159,'','',0,3); INSERT INTO parsefileTBL VALUES (160,'','',1,4); -- -- Table structure for table `parsefilestatusTBL` -- CREATE TABLE parsefilestatusTBL ( userID int(5) NOT NULL default '0', testdate timestamp(14) NOT NULL, action int(5) NOT NULL default '0', statusID int(5) NOT NULL default '0', fileID int(10) NOT NULL default '0', UNIQUE KEY fileID (fileID,testdate) ) TYPE=BerkeleyDB; -- -- Dumping data for table `parsefilestatusTBL` -- -- -- Table structure for table `universityTBL` -- CREATE TABLE universityTBL ( name varchar(50) NOT NULL default '', city varchar(20) default '', stateVAL varchar(5) NOT NULL default '', userID int(10) NOT NULL default '0', ID int(10) NOT NULL auto_increment, PRIMARY KEY (ID), UNIQUE KEY name (name) ) TYPE=BerkeleyDB; -- -- Dumping data for table `universityTBL` -- INSERT INTO universityTBL VALUES ('Auburn','city','state',0,40); -- -- Table structure for table `university_urlTBL` -- CREATE TABLE university_urlTBL ( universityID int(10) NOT NULL default '0', urltype int(5) NOT NULL default '0', url varchar(50) NOT NULL default '', userID int(10) NOT NULL default '0', actionID int(5) default '0', status int(5) default '0', ID int(10) NOT NULL auto_increment, PRIMARY KEY (ID), UNIQUE KEY url (url,universityID,urltype) ) TYPE=BerkeleyDB; -- -- Dumping data for table `university_urlTBL` -- INSERT INTO university_urlTBL VALUES (40,0,'url1',0,0,0,157); INSERT INTO university_urlTBL VALUES (40,0,'url2',0,0,0,158); INSERT INTO university_urlTBL VALUES (40,0,'url3',0,0,0,159); INSERT INTO university_urlTBL VALUES (40,0,'url4',0,0,0,160); -- -- Table structure for table `userTBL` -- CREATE TABLE userTBL ( name varchar(20) NOT NULL default '', email varchar(20) NOT NULL default '', phone varchar(20) NOT NULL default '', city varchar(20) NOT NULL default '', state varchar(20) NOT NULL default '', usergroup varchar(10) NOT NULL default '', userlevel varchar(10) NOT NULL default '', ID int(5) NOT NULL auto_increment, UNIQUE KEY ID (ID) ) TYPE=BerkeleyDB; -- -- Dumping data for table `userTBL` -- INSERT INTO userTBL VALUES ('tom','','','','','','',1); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
