Finding if record does not exist

Shroder

I have two tables. One is a list of files that have been uploaded and the other is a table that contains 'assignments'. This is to keep track of who is assigned to what file.

Right now there is no record in the assignments table if the file has not been assigned yet.

I need to show all files that do not have a record in the assignment table or if they are in the assignment table and do not have a value of 2 in the 'status' collumn.

I'm going to look in mysql.com a little more but I was hoping someone may be able to give me a quick answer.

Thanks!

Drakla

SELECT comps.name from companies comps LEFT JOIN cats ON comps.categoryid=cats.categoryid WHERE cats.categoryid IS NULL

This is a left join that will find company names that have a category id that is NOT in the category (cats) table, and would therefore be orphaned in some types of searching.

Just modify it for your two tables. This type of query crops up all the time in this forum, do a search on left joins or "Lord Shryuku" for tip top info.

Shroder

Thank you for you help.

The only problem I have with that right now is that the id isn't picked up if there is no record in the second database.

I tried it in mysql directly and it looks like the ids are showing up. When done in php its null though.

Anyway around this or is there possibly something I'm doing wrong? I'll post the query string.

SELECT * FROM `upload_history` LEFT JOIN `users` ON (`users`.`user_id` = `upload_history`.`user_id`) LEFT JOIN `assignments` ON (`assignments`.`upload_id` = `upload_history`.`upload_id`) WHERE `complete` = '1' AND `assignments`.`status` !='2' OR `assignments``status` is NULL ORDER BY `upload_ftimestamp`;

Shroder

I figured out a temp fix. file_id doesn't show up but 0 does (using fetch array)