Re: MySQL JOIN problem

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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" <rday@tsl.state.tx.us> wrote in message
1863ABF011CCD611BE5B00065B3D8A5720F734@Exchange.tsl.state.tx.us">news:1863ABF011CCD611BE5B00065B3D8A5720F734@Exchange.tsl.state.tx.us...
> 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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux