> I need to join table 1 to table 2 -- and then table 2 back to table 1 > -- in the same query. While I think this is possible, I am having > difficulty achieving it. > > The background info: > > I have a bunch of files that have relationships with each other. Right > now I have two tables: > > 1) has: file_id, file_title, file_type, file_etc. > 2) has: file_id, related_file_id > > the second table has only two columns which are together the unique > identifier -- this is because I have a many-to-many relationship between > files. > > As best as I can describe, I need to get the following information about > a file: > > Get the file_id and other info, as well as relationships it has with > other files -- and the file_id and other info about the related files. > > Under the current table structure, I need to join table a to table b to > table a. I would prefer to do this in one query. I _can_ change the > table layout if necessary.
That should be pretty easy: Assuming table a is 'files' and table b is 'files_relations': SELECT files.*, related.* FROM files INNER JOIN files_relations ON files.file_id = files_relations.file_id INNER JOIN files as related ON files_relations.related_id = related.file_id Or you could do: SELECT files.*, related.* FROM files, files_relations, files as related WHERE files.file_id = files_relations.file_id AND files_relations.related_id = related.file_id Jon ____________________ BYU Unix Users Group http://uug.byu.edu/ ___________________________________________________________________ List Info: http://uug.byu.edu/cgi-bin/mailman/listinfo/uug-list
