this will gives you 4 records for this particular school...
select * from university_urlTBL where university_urlTBL.universityID='40'; +--------------+---------+------------------------+--------+----------+----- ---+-----+ | universityID | urltype | url | userID | actionID | status | ID | +--------------+---------+------------------------+--------+----------+----- ---+-----+ | 40 | 1 | http://www.auburn.edu | 0 | 0 | 0 | 157 | | 40 | 2 | http://www.auburn.edu2 | 0 | 0 | 0 | 158 | | 40 | 3 | http://www.auburn.edu3 | 0 | 0 | 0 | 159 | | 40 | 4 | http://www.auburn.edu4 | 0 | 0 | 0 | 160 | +--------------+---------+------------------------+--------+----------+----- ---+-----+ 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, urltype), | 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; create table 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; here's the rough structure of the db/tables... right now i have ~2000 records in the universityTBL... -----Original Message----- From: Lachlan Mulcahy [mailto:[EMAIL PROTECTED] Sent: Monday, August 09, 2004 6:02 PM To: [EMAIL PROTECTED] Subject: RE: left join issues!!! Bruce, What do you get when you do this: SELECT * FROM university_urlTBL WHERE universityID = 40; Lachlan -----Original Message----- From: bruce [mailto:[EMAIL PROTECTED] Sent: Tuesday, 10 August 2004 10:46 AM To: 'Lachlan Mulcahy' Subject: RE: left join issues!!! i tried your suggestion.... i got the following... select p2.statusID as parseStatus, -> p2.action as parseAction, -> 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 -> where u1.ID = '40'; +-------------+-------------+--------+------+-----------+---------+--------+ -------------------+-------+ | parseStatus | parseAction | userID | ID | schoolUrl | urlType | school | city | state | +-------------+-------------+--------+------+-----------+---------+--------+ -------------------+-------+ | NULL | NULL | NULL | NULL | NULL | NULL | www | Auburn University | AL | +-------------+-------------+--------+------+-----------+---------+--------+ -------------------+-------+ which is not what i was going for !!!!! -----Original Message----- From: Lachlan Mulcahy [mailto:[EMAIL PROTECTED] Sent: Monday, August 09, 2004 5:21 PM To: [EMAIL PROTECTED] Subject: RE: left join issues!!! Hi Bruce, I haven't reconstructed your database to test for sure, but I think your problem is stemming from the way your joins are working. First of all, since you are left joining to the university_urlTBL, you will also need to left join to any tables you join from that table. If you don't do this then when a record is not found in the university_urlTBL you'll get your NULLs which you are then doing an inner join on to the parsefileTBL,.. subsequently there will be no record in the parsefileTBL matching the NULL and in turn you will lose the whole record. This seems to negate the point of the LEFT JOIN in the first place since unless a real record is found in university_urlTBL to match you won't get a result anyhow. Essentially you want to change all your joins to left join and change your WHERE clause to refer to the universityTBL only. Try this: 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 ul 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'; As I said, I haven't reconstructed the database, but this is the way I would try,.. As a general rule if you are chaining up your joins, every join after the first left join should also be a left join unless of course you plan on joining on NULLs. Hope this helps :) Regards, Lachlan -----Original Message----- From: bruce [mailto:[EMAIL PROTECTED] Sent: Tuesday, 10 August 2004 7:16 AM To: [EMAIL PROTECTED] Subject: left join issues!!! 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] -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
