hi...
sorry for the length of this post!!
i have the following, that i somehow managed to get working at some point,
but i've screwed it up!!!
basically i have a number of tables, that i'm trying to join. the tables
are:
universityTBL(
name varchar(50) not null default '',
city varchar(20) default '',
stateVAL varchar(5) not null,
userID int(10) not null default'',
ID int(10) not null auto_increment,
primary key (ID),
unique key (name)
)type =bdb;
university_urlTBL(
universityID int(10) not null default '',
urltype int(5) not null,
url varchar(50) not null default '',
userID int(10) not null default'',
actionID int(5) null default '',
status int(5) null default '',
ID int(10) not null auto_increment,
primary key (ID),
unique key (url, universityID, urltype)
)type =bdb;
userTBL(
name varchar(20) not null,
email varchar(20) not null,
phone varchar(20) not null,
city varchar(20) not null,
state varchar(20) not null,
usergroup varchar(10) not null,
userlevel varchar(10) not null,
ID int(5) not null auto_increment,
unique key (ID)
)type =bdb;
parsefileTBL(
university_urlID int(5) not null default '',
filelocation varchar(50) not null default '',
name varchar(50) not null default '',
-- urltype int(2) not null,
userID int(10) not null default '',
fileID int(10) not null auto_increment,
primary key (fileID),
unique key (university_urlID, filelocation)
)type =bdb;
parsefilestatusTBL(
userID int(5) not null default '',
testdate timestamp not null,
action int(5) not null default '',
statusID int(5) not null default '',
fileID int(10) not null,
unique key (fileID, testdate)
)type =bdb;
each table links:
universityTBL.ID -> university_urlTBL.universityID
university_urlTBL.ID -> parsefileTBL.university_urlID
parsefileTBL.fileID -> parsefilestatusTBL.fileID
userTBL.ID -> parsefileTBL.userID
universityTBL has a unique ID
within the university_urlTBL, the ID from the universityTBL might point to
multiple records (basically, a university might have multiple urls that
we're going to track)
there might be multiple records within the parsefileTBL to track to a given
record within the university_urlTBL
the parsefilestatusTBL will have multiple records for each record within the
parsefileTBL that it links/joins on. this table basically provides ongoing
status for the changes made to the underlying docs within the
parsefileTBL...
the userTBL has a unique ID for each record, which is referenced within
other tables...
i'm simply trying to create a select that provides me with data from each
table, such that i can do something like:
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 u3.universityID = u1.ID
join parsefileTBL as p1
on p1.university_urlID=u3.ID
join parsefilestatusTBL as p2
on p2.fileID = p1.fileID
left join userTBL as u2
on u2.ID = p1.userID
where u3.universityID='40';
this sortof works, but the 'name' from the userTBL is listed as 'NULL'. in
fact i should have a list of '4' items, with a defined user for one of the
items, and NULL for the other 3 as no user has yet been defined!! it appears
that the information for the userID is being returned correctly, as i can
display the userID as being NULL for the items that haven't defined a user,
and the userID is set for the item where the user has been defined....
it should be doable, to create the 'select' using 'left join' statements,
and in fact i had it working, or at least i thought i did... but i somehow
screwed something up...
thanks for any help/assistance/pointers....!!!
-bruce
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
