Joining tables in several ways withing the same query

We have an appointment and scheduling application with the following
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated :)Hi
Try using UNIONs. If you are not comfortable in using them, please send the
DDL so that any one can post a query to you.
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"hsifelbmur" wrote:
> We have an appointment and scheduling application with the following
> structure:
> Appointments - a table containing appointment information;
> Phonebook - a table containing information about people;
> Users - a table with a foreign key to Phonebook, defining specific
> Phonebook entries as system users.
> The table Appointments is linked many-to-many, via a junction table, to
> Phonebook, determining the participants in an appointment. Is is also
> linked, through a second junction table, to Users, determining the
> appointment participants who are system users (and can therefore change
> details of the meeting, accept/decline their participation, etc).
> My questions is: We retrieve details about meetings (basically a daily
> calendar display) using one query, joining the different tables
> mentioned above. Since participant's names all come from Phonebook, how
> can I, in the query's result set, distinguish system participants from
> other participants? Although they are joined into the result set
> through two different tables, they all end up as one field.
> Any advice will be appreciated :)
>|||You can reference 2 different copies of the same table in a query via an
alias. You didn't post DDL, so I'll use the Employees table in Northwind.
Here, you want the employee name and manager name:
select
e.LastName Employee
, m.LastName Manager
from
dbo.Employees e
join dbo.Employees m on m.EmployeeID = e.ReportsTo
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"hsifelbmur" <aquarian1978@.yahoo.com> wrote in message
news:1116225930.258119.295960@.g43g2000cwa.googlegroups.com...
We have an appointment and scheduling application with the following
structure:
Appointments - a table containing appointment information;
Phonebook - a table containing information about people;
Users - a table with a foreign key to Phonebook, defining specific
Phonebook entries as system users.
The table Appointments is linked many-to-many, via a junction table, to
Phonebook, determining the participants in an appointment. Is is also
linked, through a second junction table, to Users, determining the
appointment participants who are system users (and can therefore change
details of the meeting, accept/decline their participation, etc).
My questions is: We retrieve details about meetings (basically a daily
calendar display) using one query, joining the different tables
mentioned above. Since participant's names all come from Phonebook, how
can I, in the query's result set, distinguish system participants from
other participants? Although they are joined into the result set
through two different tables, they all end up as one field.
Any advice will be appreciated :)