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]
