> The solution below is helpful, but it only pulls out the records where > there is a relationship. What I need is the ability to pull out all > records, regardless of whether or not they have file relationships - but > when there is a relationship, I need to referecnce the original table to > retrive information about the relating file. > > Dave > > >>> [EMAIL PROTECTED] 02/04/04 11:42 AM >>> > In MySQL (and AFAIK, SQL in general) you can specify a table multiple > times. So for your problem the following should work fine: > > ~ select f1.*, f2.* from file f1, file_relation r, file f2 > ~ where f1.file_id=? and r.file_id=f1.file_id and > ~ f2.file_id=r.related_file_id >
Try: SELECT f1.*, f2.* FROM file f1, LEFT JOIN file_relation r ON r.file_id=f1.file_id, LEFT JOIN file f2 ON f2.file_id=r.related_file_id For ones that have relationships, you'll get results like this: f1.file_id: 7 f1.file_name: 'main.txt' f1.file_type: 'txt' f2.file_id: 11 f2.file_name: 'other.txt' f2.file_type: 'txt' For ones that don't have relationships, you'll get results like this: f1.file_id: 8 f1.file_name: 'additional.txt' f1.file_type: 'txt' f2.file_id: NULL f2.file_name: NULL f2.file_type: NULL ____________________ BYU Unix Users Group http://uug.byu.edu/ ___________________________________________________________________ List Info: http://uug.byu.edu/cgi-bin/mailman/listinfo/uug-list
