>Hi,
> Thanks for the reply.
>
>The sites (deptsite1 deptsite2) on the department file are the numeric key
>to the actual site file.
>
>The query below works fine (in mysql & php) but how do I reference the
>sitename for the joins that have happened?
>
>Specifying
> $result = mysql_db_query($dbName,$query);
> while ($r=mysql_fetch_array($result))
> echo "<td><font size=\"-1\">$r[sitename]</td>
>in php returns the last sites name for all
>
>I thought mabe you could say S1.sitename but that doesn't seem to work.
>
>The last thought that occured to me is to scrap the numeric key from the
>site file altogether & just have the name as the key.
>
>I've only been playing with MySQL & PHP for a couple of weeks, I don't
>know how to 'normalize' a table or what that means. Sorry.
I don't know the details of your circumstances, so you may need to
alter this. Normalizing the department table as it stands would
result in:
Depts
-----
dept_id
department
Join_Depts_Sites
----------------
dept_id
site_id
Sites
-----
site_id
site
The table Join_Dept_Sites is a join table. It creates a many-to-many
join between Depts and Sites. To get a list of all departments at all
sites, use
SELECT Depts.departement, Sites.site
FROM (Depts INNER JOIN Join_Depts_Sites
ON Depts.dept_id = Join_Depts_Sites.dept_id
) INNER JOIN Sites ON Join_Depts_Sites.site_id = Sites.site_id;
Note the formatting, especially the capitalization. Separating the
clauses and putting the keywords in all upper case makes it easier
for us to understand your SQL statements and offer help. It will also
make it easier for you to understand your own statements when you
need to do maintenance six months from now.
You will need to have both a Dept column and a Site column in your
employee table, containing the department and site IDs. Getting a
list of all employees in a specific department at a specific site
becomes very easy.
SELECT Surname, Firstname
FROM Employees
WHERE Dept = $dept AND Site = $site;
If you're going to set up databases, you should get a book on
relational databases. There's a number of books on the market, and
the one's I've seen all seem to be equally good. I own a copy of
'Relational Databases Clearly Explained' by Jan Harrington, publisher
Morgan Kaufman, and it seems to be as good as any other I've seen.
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
MySQL list magic words: sql query database
---------------------------------------------------------------------
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
