Professional Web Applications Themes

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

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, ...

  1. #1

    Default 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.


    --
    P.A. Guest

  2. #2

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

    On Jul 23, 12:13 pm, "P.A." <com> wrote: 

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

    ZeldorBlat Guest

  3. #3

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

    Am 23.07.2007, 18:52 Uhr, schrieb ZeldorBlat <com>:
     
    >
    > 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
    > ...
    >[/ref]

    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.

    --
    P.A. Guest

Similar Threads

  1. Update art "under" my form fields?
    By Creephead@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 4
    Last Post: April 29th, 08:22 PM
  2. comparing 2 "bigint" fields
    By Dan in forum ASP Database
    Replies: 6
    Last Post: October 13th, 11:09 AM
  3. Replies: 2
    Last Post: September 15th, 04:35 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139