Leif,
> I'm not sure what you mean by "tbl2's population is a sub-set of that
> in tbl1". tbl1 contains user id nums and hours each user spent
working on a
> project. tbl2 contains user id nums and the first and last names that
> correspond to each user id. There are user id nums in tbl1 that do
not
> exist in tbl2 (probably because the users are no longer work here).
Hope
> that helps clarify.
>
> The query you suggested is not working the way I hoped it would. I
just
> want to select the hours from tbl1 and the first and last name from
tbl2
> where the user id from tbl1 is the same as the user id from tbl2. If,
the
> user id in tbl1 does not exist in tbl2 I want MySQL to return 'no
record'.
>
>
> NATHAN-
>
> Because I'm selecting multiple user ids in the query, some of which
exist in
> both tables, some of which do not, MySQL only returns the entries that
exist
> in both tables with a "SELECT tbl1.id, tbl1.data, tbl2.name FROM tbl1,
tbl2
> WHERE tbl1.id = tbl2.id;" That means I'm not getting all the data
from tbl1
> and I need to get all the data from tbl1 whether or not the id #
exists in
> tbl2. Hope this helps clarify.
As you observe to Nathan, if the query is an INNER JOIN (explicit or
implicitly) then only the intersection set will appear in the resultset,
ie rows which appear in both tables. As well as that, you wanted those
rows that appear in one tbl, but not in the other, to be included in the
resultset - but dealt with differently.
Let's take that as two steps: first off, how to get them included. This
means that there are members of tbl1 that are not also members of tbl2 -
this is what my earlier talk of "populations" was about. An OUTER JOIN
will include not only members of the intersection/both tables, but
members of the populations of both tables that do not have a
corresponding/related row in the other table. However if one table
contains 'all' the column values and the other only has values in the
join-column that are a subset, there's not much point in checking for
the situation where its members might not have a corresponding value/row
in the other table (which also takes query response time). So if the
'master' table is on the left, and the 'subset' on the right (of the
join condition it is called a LEFT OUTER JOIN - if the tables are the
other way around with the 'master' on the right...well you get the idea.
So run the SELECT with a LEFT OUTER JOIN between the tables. Where there
is a row value on the 'left' that has no corresponding value in the
table on the 'right' you should see gaps, zeros, or NULLs (varying by
data type and MySQL client used). Working correctly so far?
Now let's look at the second step: how to get those 'spaces' converted
to instead say "no record". If the subset table is able to provide a
value, you want that value. If the subset table has no row/value to make
the join, then you want the 'marker' text:
IF( Jsubset.Word > '', Jfull.Word, 'no record' )
Please substitute your own column names. This says if the column in the
subset (right side) table has some value, use the value (above I've said
use the one from the left table, but you could use the one from the
right table just as easily if they are the same! On the other hand, if
there is no string value because there is no corresponding/join row in
the right table (the value is therefore NULL), SQL uses the 'else'
portion of the if and plugs "no record" into the field as a position
'holder'.
NB I checked that the code provided works before I sent it to you. Is
the way it fits together/works clear now? If not, please present your
implementation in SQL and list its short-comings compared to what you
really want ("not working" is gives too little to work on!?), and
illustrate with sample data.
Regards,
=dn
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
