SELECT a.report_id, a.lib_id, a.exclude, b.lib_name, b.city FROM reportinfo a LEFT JOIN libinfo b ON a.lib_id = b.lib_id WHERE reportinfo.quarter != '0'
the above statement will return ALL values within the "reportinfo" table that have a quarter value NOT set to 0. it will return the relevant lib_name and city from the "libinfo" table, through the left join. if you want to only display reportinfo items that have a libinfo entry, you will want to use the INNER JOIN instead. also note that the aliases of 'a' and 'b' correspond to reportinfo and libinfo. They are set simply by declaring the alternate name after first referencing the tables hope this helps, Cam "Rob Day" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I'm having trouble getting the results I want. The database deals with > libraries and reports they've submitted. Here are the relevant tables with > the primary keys marked with a star (*): > > +--------------+ > | libinfo | > +--------------+ > | lib_id* | > | lib_name | > | city | > +--------------+ > > +---------------+ > | reportinfo | > +---------------+ > | report_id* | > | lib_id | > | exclude | > | quarter | > +---------------+ > > In reportinfo.quarter there are currently two possible values, 0 and 2. I > want libinfo.lib_name and libinfo.city for all entries in libinfo, where > libinfo.lib_id = reportinfo.lib_id, that do not have an entry in reportinfo > where reportinfo.quarter = 0. I don't care if there is an entry in > reportinfo where quarter = 2. Can someone please help me construct this > query? Thanks. > -Rob > > P.S. I realize that this question has nothing to do with PHP. But be assured > that this is one small part of a PHP/MySQL web application. Thank you for > your indulgence. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
