[PHP-DB] Re: MySQL JOIN problem
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
[PHP-DB] Re: MySQL JOIN problem
too busy or lazy to write it out right now but hereTaken from page : http://www.mysql.com/doc/en/JOIN.htmlmysql SELECT table1.* FROM table1 -LEFT JOIN table2 ON table1.id=table2.id -WHERE table2.id IS NULL; This example finds all rows in table1 with an id value that is not present in table2 (that is, all rows in table1 with no corresponding row in table2). This assumes that table2.id is declared NOT NULL, of course. See section 5.2.6 How MySQL Optimises LEFT JOIN and RIGHT JOIN. right from the MYSQL site, it is sorta the struct u need. play with it a little to get values that also have a '2' and not a 0. Joel 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
