Am 23.07.2007, 18:52 Uhr, schrieb ZeldorBlat <zeldorblat@gmail.com>:

> On Jul 23, 12:13 pm, "P.A." <anscho...@yahoo.com> wrote:
>> Hello,
>>
>> I have a table with staff data, containing the usual id, name, address,
>> etc. Moreover there are some fields (e.g. for nationality, rel.
>> denomination, title, function) which hold integer or two-character
>> indices
>> rather than the actual value. The actual values are contained in
>> separate
>> tables. I do this for ease and safety of input checking in php. If the
>> data of a particular staff member is to be reported, I would like to
>> have
>> a single query for the whole row, including the referenced values
>> instead
>> of the indices itself.
>>
>> Example:
>>
>> table staff(staff_id, staff_name, staff_title, staff_nat,
>> staff_function,...);
>> entry: (23, Wilcox, 002, 017, SU, ...);
>>
>> table titles(title_id, title);
>> entry: (002, Dr.);
>>
>> table nats(nat_id, nat);
>> entry: (017, Bahamas);
>>
>> table funcs(func_id, func);
>> entry: (SU, Surgeon);
>>
>> and so on...
>>
>> How could I query the row from the staff table, replacing the indicesby
>> the referenced values?
>>
>> I tried it with the UNION operator, but this gives a columns of results,
>> not a row, if I'm not mistaken.
>>
>> Any hint is very much appreciated!
>>
>> Thanks!
>>
>> Pascal.
>>
>> --
>
> You want to "join" the various tables together:
> <http://dev.mysql.com/doc/refman/5.0/en/join.html>
>
> Something like this:
>
> select s.staff_id, s.staff_name,
> t.title,
> n.nat,
> ...
> from staff s
> join titles t on s.staff_title = t.title_id
> join nats on s.staff_nat = n.nat_id
> ...
>

Thank you! That was the first step. Now to make things more complicated:
As the staff_function (here: SU) is part of the identification and
authorization process, I have another table containing authentication
related data:

table auth(staff_id, staff_login, staff_pw, staff_func1, staff_func2)

Table staff does not hold the function info any longer. Now, I have two
tables involved which have referencing indices: staff and auth, which I
still want to join together. That does not seem to work in the proposed
way. I tried:

"SELECT s.staff_id, s.staff_name, ...
n.nat,
a.staff_func1, a.staff_func2, a.staff_login, ...
...
FROM staff s
INNER JOIN auth a ON s.staff_id = {$some_id} AND a.staff_id = {$some_id}
INNER JOIN nats n ON s.staff_nat = n.nat_id
INNER JOIN funcs f1 ON a.staff_func1 = f1.func_id
INNER JOIN funcs f2 ON s.staff_func2 = ???";...

And here I am stuck: How am I supposed to get a) the actual string from
table funcs when I need to refer to the alias 'a' from an earlier JOIN and
b) how would I do it for function 2 again?

Do I need some kind of sub query here? I just can't an idea for this from
the manuals's "join" page.

Thanks again in advance!

Pascal.

--