How to combine "regular" fields and fields with references in one row?

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 indices by
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.


--

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 indices by
> 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
...

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.

--