Tutor Project - more

What I am developing is a system to match Tutors and
Tutees based on Interest, Days, and Time. I'm having
trouble matching the two based on Interest and Time and
Days. I finally have most of the foundation laid. Here
is what I have:

----------start of database layout and sample data --------
Tutor(
TutorID,
TutorName,
TutorAddress,
TutorCity,
TutorState,
TutorZip
)
1,Tony Johnson,55 Street,Los Angeles,CA,90211
2,Sarah Tom,2834 Main St.,Dayton,OH,45438
3,Gregory Zacks,5884 Lane Ave.,Miami,FL,59388

Tutee(
TuteeID,
TuteeName,
TuteeAddress,
TuteeCity,
TuteeState,
TuteeZip )
1,Mary Smith,642 Tinker St.,Columbus,OH,43201
2,Thomas Bradley,11 Short St.,Augusta,GA,64738
3,Jane Powell,847 Pine Rd.,Cleveland,OH,47372


Interest (
InterestID,
Interest,
InterestDescription)
1,Playing Hockey,Playing ice hockey.
2,Watching Baseball,Watching baseball at live game.
3,Browsing Internet,Browing Internet.

TimeofDay (
TimeOfDayID,
TimeOfDay)
1,Mornings
2,Afternoon
3,Evening
4,Flexible


DayOfWeek (
DayOfWeekID,
DayofWeek)
1,Monday
2,Tuesday
3,Wednesday
4,Thursday
5,Friday

TutorInterest(
TutorID,
InterestID)
1,1
1,2

TuteeInterest (
TuteeID,
InterestID)
2,1
3,1

TutorDay (
TutorID,
DayOfWeekID )
1,1
1,2
1,3
1,4
2,1
2,2
2,3
2,4
3,1
3,2
3,3
3,4

TuteeDay (
TuteeID,
DayOfWeekID)
1,1
1,2
1,3
1,4
2,1
2,2
2,3
2,4
3,1
3,2
3,3
3,4

TutorTime(
TutorID,
TutorTimeOfDayID)
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3

TuteeTime(
TuteeID,
TuteeTimeOfDayID)
1,1
1,2
1,3
2,1
2,2
2,3
3,1
3,2
3,3
-----------end of database layout and sample data ---------
Here is what I have for my join:
select Tutor.name TutorName, Tutee.name TuteeName
from Tutor
join TutorInterest on (Tutor.TutorID =
TutorInterest.TutorID)
join TuteeInterest on (TutorInterest.InterestID =
TuteeInterest.InterestID)
join Tutee on (Tutee.TuteeID = TuteeInterest.TuteeID)
join TutorDay on (Tutorday.TutorID = Tutor.TutorID)
join TuteeDay on (TutorDay.dayofweekID =
TuteeDay.dayofweekID)
join TutorTime on (TutorTime.TutorID = Tutor.TutorID)
join TuteeTime on (TutorTime.TimeOfDayID =
TuteeTime.TimeOfDayID)

It seems to work, but it brings back multiples of the same
name. What I want to know is if this is the best way to
retrieve matches between tutors and tutees based on
interest, time, and days? Also, how could I find a match
if the TutorID was given?

Any and all help will be greatly appreciated.

SQL Server 2000 Enterprise Version

I'm using SQL Server 2000 Enterprise Version with ASP
version 2, I believe. Windows 2000 Server is the server
software. Any help would be GREATLY appreciated.

Mike
>-----Original Message-----
>What I am developing is a system to match Tutors and
>Tutees based on Interest, Days, and Time. I'm having
>trouble matching the two based on Interest and Time and
>Days. I finally have most of the foundation laid. Here
>is what I have:
>
>----------start of database layout and sample data -------
-
>Tutor(
>TutorID,
>TutorName,
>TutorAddress,
>TutorCity,
>TutorState,
>TutorZip
>)
>1,Tony Johnson,55 Street,Los Angeles,CA,90211
>2,Sarah Tom,2834 Main St.,Dayton,OH,45438
>3,Gregory Zacks,5884 Lane Ave.,Miami,FL,59388
>
>Tutee(
>TuteeID,
>TuteeName,
>TuteeAddress,
>TuteeCity,
>TuteeState,
>TuteeZip )
>1,Mary Smith,642 Tinker St.,Columbus,OH,43201
>2,Thomas Bradley,11 Short St.,Augusta,GA,64738
>3,Jane Powell,847 Pine Rd.,Cleveland,OH,47372
>
>
>Interest (
>InterestID,
>Interest,
>InterestDescription)
>1,Playing Hockey,Playing ice hockey.
>2,Watching Baseball,Watching baseball at live game.
>3,Browsing Internet,Browing Internet.
>
>TimeofDay (
>TimeOfDayID,
>TimeOfDay)
>1,Mornings
>2,Afternoon
>3,Evening
>4,Flexible
>
>
>DayOfWeek (
>DayOfWeekID,
>DayofWeek)
>1,Monday
>2,Tuesday
>3,Wednesday
>4,Thursday
>5,Friday
>
>TutorInterest(
>TutorID,
>InterestID)
>1,1
>1,2
>
>TuteeInterest (
>TuteeID,
>InterestID)
>2,1
>3,1
>
>TutorDay (
>TutorID,
>DayOfWeekID )
>1,1
>1,2
>1,3
>1,4
>2,1
>2,2
>2,3
>2,4
>3,1
>3,2
>3,3
>3,4
>
>TuteeDay (
>TuteeID,
>DayOfWeekID)
>1,1
>1,2
>1,3
>1,4
>2,1
>2,2
>2,3
>2,4
>3,1
>3,2
>3,3
>3,4
>
>TutorTime(
>TutorID,
>TutorTimeOfDayID)
>1,1
>1,2
>1,3
>2,1
>2,2
>2,3
>3,1
>3,2
>3,3
>
>TuteeTime(
>TuteeID,
>TuteeTimeOfDayID)
>1,1
>1,2
>1,3
>2,1
>2,2
>2,3
>3,1
>3,2
>3,3
>-----------end of database layout and sample data --------
-
>Here is what I have for my join:
>select Tutor.name TutorName, Tutee.name TuteeName
>from Tutor
>join TutorInterest on (Tutor.TutorID =
>TutorInterest.TutorID)
>join TuteeInterest on (TutorInterest.InterestID =
>TuteeInterest.InterestID)
>join Tutee on (Tutee.TuteeID = TuteeInterest.TuteeID)
>join TutorDay on (Tutorday.TutorID = Tutor.TutorID)
>join TuteeDay on (TutorDay.dayofweekID =
>TuteeDay.dayofweekID)
>join TutorTime on (TutorTime.TutorID = Tutor.TutorID)
>join TuteeTime on (TutorTime.TimeOfDayID =
>TuteeTime.TimeOfDayID)
>
>It seems to work, but it brings back multiples of the
same
>name. What I want to know is if this is the best way to
>retrieve matches between tutors and tutees based on
>interest, time, and days? Also, how could I find a match
>if the TutorID was given?
>
>Any and all help will be greatly appreciated.
>.
>

Re: Tutor Project - more

I can't finish addressing this until tonight, I did start looking at it, and
have put a couple comments inline with the text. I will get back to it
tonight.

Bob Barrows
Mike Williams wrote:
> What I am developing is a system to match Tutors and
> Tutees based on Interest, Days, and Time. I'm having
> trouble matching the two based on Interest and Time and
> Days. I finally have most of the foundation laid. Here
> is what I have:
>
> ----------start of database layout and sample data --------
> Tutor(
> TutorID,
> TutorName,
> TutorAddress,
> TutorCity,
> TutorState,
> TutorZip
> )
> 1,Tony Johnson,55 Street,Los Angeles,CA,90211
> 2,Sarah Tom,2834 Main St.,Dayton,OH,45438
> 3,Gregory Zacks,5884 Lane Ave.,Miami,FL,59388
>
> Tutee(
> TuteeID,
> TuteeName,
> TuteeAddress,
> TuteeCity,
> TuteeState,
> TuteeZip )
> 1,Mary Smith,642 Tinker St.,Columbus,OH,43201
> 2,Thomas Bradley,11 Short St.,Augusta,GA,64738
> 3,Jane Powell,847 Pine Rd.,Cleveland,OH,47372
>

I would probably combine these into a single table, adding a column to
indicate whether the person is a tutor, tutee, or both.

>
> Interest (
> InterestID,
> Interest,
> InterestDescription)
> 1,Playing Hockey,Playing ice hockey.
> 2,Watching Baseball,Watching baseball at live game.
> 3,Browsing Internet,Browing Internet.
>
> TimeofDay (
> TimeOfDayID,
> TimeOfDay)
> 1,Mornings
> 2,Afternoon
> 3,Evening
> 4,Flexible
>
>
> DayOfWeek (
> DayOfWeekID,
> DayofWeek)
> 1,Monday
> 2,Tuesday
> 3,Wednesday
> 4,Thursday
> 5,Friday
>
> TutorInterest(
> TutorID,
> InterestID)
> 1,1
> 1,2
>
> TuteeInterest (
> TuteeID,
> InterestID)
> 2,1
> 3,1

Again, these could probably be combined into a single table ...
The same with the following two.

>
> TutorDay (
> TutorID,
> DayOfWeekID )
> 1,1
> 1,2
> 1,3
> 1,4
> 2,1
> 2,2
> 2,3
> 2,4
> 3,1
> 3,2
> 3,3
> 3,4
>
> TuteeDay (
> TuteeID,
> DayOfWeekID)
> 1,1
> 1,2
> 1,3
> 1,4
> 2,1
> 2,2
> 2,3
> 2,4
> 3,1
> 3,2
> 3,3
> 3,4
>
> TutorTime(
> TutorID,
> TutorTimeOfDayID)
> 1,1
> 1,2
> 1,3
> 2,1
> 2,2
> 2,3
> 3,1
> 3,2
> 3,3
>
> TuteeTime(
> TuteeID,
> TuteeTimeOfDayID)
> 1,1
> 1,2
> 1,3
> 2,1
> 2,2
> 2,3
> 3,1
> 3,2
> 3,3
> -----------end of database layout and sample data ---------
> Here is what I have for my join:
> select Tutor.name TutorName, Tutee.name TuteeName
> from Tutor
> join TutorInterest on (Tutor.TutorID =
> TutorInterest.TutorID)
> join TuteeInterest on (TutorInterest.InterestID =
> TuteeInterest.InterestID)
> join Tutee on (Tutee.TuteeID = TuteeInterest.TuteeID)
> join TutorDay on (Tutorday.TutorID = Tutor.TutorID)
> join TuteeDay on (TutorDay.dayofweekID =
> TuteeDay.dayofweekID)
> join TutorTime on (TutorTime.TutorID = Tutor.TutorID)
> join TuteeTime on (TutorTime.TimeOfDayID =
> TuteeTime.TimeOfDayID)
>
> It seems to work, but it brings back multiples of the same
> name. What I want to know is if this is the best way to
> retrieve matches between tutors and tutees based on
> interest, time, and days? Also, how could I find a match
> if the TutorID was given?
>
> Any and all help will be greatly appreciated.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Tutor Project - more

Mike Williams wrote:
> What I am developing is a system to match Tutors and
> Tutees based on Interest, Days, and Time. I'm having
> trouble matching the two based on Interest and Time and
> Days. I finally have most of the foundation laid. Here
> is what I have:
>

> -----------end of database layout and sample data ---------
> Here is what I have for my join:
> select Tutor.name TutorName, Tutee.name TuteeName
> from Tutor
> join TutorInterest on (Tutor.TutorID =
> TutorInterest.TutorID)
> join TuteeInterest on (TutorInterest.InterestID =
> TuteeInterest.InterestID)
> join Tutee on (Tutee.TuteeID = TuteeInterest.TuteeID)
> join TutorDay on (Tutorday.TutorID = Tutor.TutorID)
> join TuteeDay on (TutorDay.dayofweekID =
> TuteeDay.dayofweekID)
> join TutorTime on (TutorTime.TutorID = Tutor.TutorID)
> join TuteeTime on (TutorTime.TimeOfDayID =
> TuteeTime.TimeOfDayID)
>
> It seems to work, but it brings back multiples of the same
> name. What I want to know is if this is the best way to
> retrieve matches between tutors and tutees based on
> interest, time, and days? Also, how could I find a match
> if the TutorID was given?
>
Here is what I've come up with:

Select distinct TutorName,TuteeName FROM (
select t.TutorID,t.Tutorname TutorName
, InterestID TutorInterest
,td.DayOfWeekID, TutorTimeOfDayID
from Tutor t inner join
TutorInterest ti on t.TutorID = ti.TutorID
inner join TutorDay td on t.TutorID = td.TutorID
inner join TutorTime tt on t.TutorID = tt.TutorID) tu
inner join (
select t.TuteeID,t.Tuteename TuteeName
, InterestID TuteeInterest
,td.DayOfWeekID, TuteeTimeOfDayID
from Tutee t inner join
TuteeInterest ti on t.TuteeID = ti.TuteeID
inner join TuteeDay td on t.TuteeID = td.TuteeID
inner join TuteeTime tt on t.TuteeID = tt.TuteeID) te
ON TutorInterest = TuteeInterest AND
tu.DayOfWeekID = te.DayOfWeekID AND
TutorTimeOfDayID = TuteeTimeOfDayID

Each subquery returns the information for the tutors and tutees
respectively. You might want to create views for each subquery.

The key is using the DISTINCT keyword to eliminate the duplicates.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Tutor Project - more

I'm trying to understand that query so I can run it. Can
you help me understand what exactly it is doing?

Mike

>-----Original Message-----
>Mike Williams wrote:
>> What I am developing is a system to match Tutors and
>> Tutees based on Interest, Days, and Time. I'm having
>> trouble matching the two based on Interest and Time and
>> Days. I finally have most of the foundation laid.
Here
>> is what I have:
>>
>
>> -----------end of database layout and sample data -----
----
>> Here is what I have for my join:
>> select Tutor.name TutorName, Tutee.name TuteeName
>> from Tutor
>> join TutorInterest on (Tutor.TutorID =
>> TutorInterest.TutorID)
>> join TuteeInterest on (TutorInterest.InterestID =
>> TuteeInterest.InterestID)
>> join Tutee on (Tutee.TuteeID = TuteeInterest.TuteeID)
>> join TutorDay on (Tutorday.TutorID = Tutor.TutorID)
>> join TuteeDay on (TutorDay.dayofweekID =
>> TuteeDay.dayofweekID)
>> join TutorTime on (TutorTime.TutorID = Tutor.TutorID)
>> join TuteeTime on (TutorTime.TimeOfDayID =
>> TuteeTime.TimeOfDayID)
>>
>> It seems to work, but it brings back multiples of the
same
>> name. What I want to know is if this is the best way
to
>> retrieve matches between tutors and tutees based on
>> interest, time, and days? Also, how could I find a
match
>> if the TutorID was given?
>>
>Here is what I've come up with:
>
>Select distinct TutorName,TuteeName FROM (
>select t.TutorID,t.Tutorname TutorName
>, InterestID TutorInterest
>,td.DayOfWeekID, TutorTimeOfDayID
>from Tutor t inner join
>TutorInterest ti on t.TutorID = ti.TutorID
>inner join TutorDay td on t.TutorID = td.TutorID
>inner join TutorTime tt on t.TutorID = tt.TutorID) tu
>inner join (
>select t.TuteeID,t.Tuteename TuteeName
>, InterestID TuteeInterest
>,td.DayOfWeekID, TuteeTimeOfDayID
>from Tutee t inner join
>TuteeInterest ti on t.TuteeID = ti.TuteeID
>inner join TuteeDay td on t.TuteeID = td.TuteeID
>inner join TuteeTime tt on t.TuteeID = tt.TuteeID) te
>ON TutorInterest = TuteeInterest AND
>tu.DayOfWeekID = te.DayOfWeekID AND
>TutorTimeOfDayID = TuteeTimeOfDayID
>
>Each subquery returns the information for the tutors and
tutees
>respectively. You might want to create views for each
subquery.
>
>The key is using the DISTINCT keyword to eliminate the
duplicates.
>
>Bob Barrows
>
>--
>Microsoft MVP - ASP/ASP.NET
>Please reply to the newsgroup. This email account is my
spam trap so I
>don't check it very often. If you must reply off-line,
then remove the
>"NO SPAM"
>
>
>.
>

Re: Tutor Project - more

I think it will be easier to understand if you create views instead of using
the subqueries. Fire up Query Analyzer and run this script:

CREATE VIEW vTutorInterestsDaysAndTimes AS
select t.TutorID,t.Tutorname TutorName
, InterestID TutorInterest
,td.DayOfWeekID, TutorTimeOfDayID
from Tutor t inner join
TutorInterest ti on t.TutorID = ti.TutorID
inner join TutorDay td on t.TutorID = td.TutorID
inner join TutorTime tt on t.TutorID = tt.TutorID
go
CREATE VIEW vTuteeInterestsDaysAndTimes AS
select t.TuteeID,t.Tuteename TuteeName
, InterestID TuteeInterest
,td.DayOfWeekID, TuteeTimeOfDayID
from Tutee t inner join
TuteeInterest ti on t.TuteeID = ti.TuteeID
inner join TuteeDay td on t.TuteeID = td.TuteeID
inner join TuteeTime tt on t.TuteeID = tt.TuteeID
go

You now have two views that you can use in your FROM clause. Run these
queries to see what the views return:

select * from vTutorInterestsDaysAndTimes
select * from vTuteeInterestsDaysAndTimes

Now, replace the subqueries in my original suggestion with these views:

Select distinct TutorName,TuteeName
FROM vTutorInterestsDaysAndTimes tu
inner join vTuteeInterestsDaysAndTimes te
ON TutorInterest = TuteeInterest AND
tu.DayOfWeekID = te.DayOfWeekID AND
TutorTimeOfDayID = TuteeTimeOfDayID

Does that help?

Bob Barrows

Mike Williams wrote:
> I'm trying to understand that query so I can run it. Can
> you help me understand what exactly it is doing?
>
> Mike
>
>> -----Original Message-----
>> Mike Williams wrote:
>>> What I am developing is a system to match Tutors and
>>> Tutees based on Interest, Days, and Time. I'm having
>>> trouble matching the two based on Interest and Time and
>>> Days. I finally have most of the foundation laid. Here
>>> is what I have:
>>>
>>
>>> -----------end of database layout and sample data ----- ----
>>> Here is what I have for my join:
>>> select Tutor.name TutorName, Tutee.name TuteeName
>>> from Tutor
>>> join TutorInterest on (Tutor.TutorID =
>>> TutorInterest.TutorID)
>>> join TuteeInterest on (TutorInterest.InterestID =
>>> TuteeInterest.InterestID)
>>> join Tutee on (Tutee.TuteeID = TuteeInterest.TuteeID)
>>> join TutorDay on (Tutorday.TutorID = Tutor.TutorID)
>>> join TuteeDay on (TutorDay.dayofweekID =
>>> TuteeDay.dayofweekID)
>>> join TutorTime on (TutorTime.TutorID = Tutor.TutorID)
>>> join TuteeTime on (TutorTime.TimeOfDayID =
>>> TuteeTime.TimeOfDayID)
>>>
>>> It seems to work, but it brings back multiples of the same
>>> name. What I want to know is if this is the best way to
>>> retrieve matches between tutors and tutees based on
>>> interest, time, and days? Also, how could I find a match
>>> if the TutorID was given?
>>>
>> Here is what I've come up with:
>>
>> Select distinct TutorName,TuteeName FROM (
>> select t.TutorID,t.Tutorname TutorName
>> , InterestID TutorInterest
>> ,td.DayOfWeekID, TutorTimeOfDayID
>> from Tutor t inner join
>> TutorInterest ti on t.TutorID = ti.TutorID
>> inner join TutorDay td on t.TutorID = td.TutorID
>> inner join TutorTime tt on t.TutorID = tt.TutorID) tu
>> inner join (
>> select t.TuteeID,t.Tuteename TuteeName
>> , InterestID TuteeInterest
>> ,td.DayOfWeekID, TuteeTimeOfDayID
>> from Tutee t inner join
>> TuteeInterest ti on t.TuteeID = ti.TuteeID
>> inner join TuteeDay td on t.TuteeID = td.TuteeID
>> inner join TuteeTime tt on t.TuteeID = tt.TuteeID) te
>> ON TutorInterest = TuteeInterest AND
>> tu.DayOfWeekID = te.DayOfWeekID AND
>> TutorTimeOfDayID = TuteeTimeOfDayID
>>
>> Each subquery returns the information for the tutors and tutees
>> respectively. You might want to create views for each subquery.
>>
>> The key is using the DISTINCT keyword to eliminate the duplicates.
>>
>> Bob Barrows
>>
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so
>> I don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM"
>>
>>
>> .

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Tutor Project - more

That worked great, and simplified it tremendously. But I
have a few questions for my own understanding.

When I used this query, I realized it did not work, it
seems logical but why didn't it work? I noticed it
picked a record that was not a match (an interest), but
the join should match them all (interest, day, time),
right?

Secondly, looking at the query you gave, its pretty deep,
its like a join that joins 2 other joins, right?

Thanks alot for your help. Now's its easy to create
queries if given a tutorID or tutee, very easy to
retrieve quick results.

--------query start------------
select Tutor.name TutorName, Tutee.name TuteeName
from Tutor
join TutorInterest on (Tutor.TutorID =
TutorInterest.TutorID)
join TuteeInterest on (TutorInterest.InterestID =
TuteeInterest.InterestID)
join Tutee on (Tutee.TuteeID = TuteeInterest.TuteeID)
join TutorDay on (Tutorday.TutorID = Tutor.TutorID)
join TuteeDay on (TutorDay.dayofweekID =
TuteeDay.dayofweekID)
join TutorTime on (TutorTime.TutorID = Tutor.TutorID)
join TuteeTime on (TutorTime.TimeOfDayID =
TuteeTime.TimeOfDayID)
--------query end --------------



>-----Original Message-----
>I think it will be easier to understand if you create
views instead of using
>the subqueries. Fire up Query Analyzer and run this
script:
>
>CREATE VIEW vTutorInterestsDaysAndTimes AS
>select t.TutorID,t.Tutorname TutorName
>, InterestID TutorInterest
>,td.DayOfWeekID, TutorTimeOfDayID
>from Tutor t inner join
>TutorInterest ti on t.TutorID = ti.TutorID
>inner join TutorDay td on t.TutorID = td.TutorID
>inner join TutorTime tt on t.TutorID = tt.TutorID
>go
>CREATE VIEW vTuteeInterestsDaysAndTimes AS
>select t.TuteeID,t.Tuteename TuteeName
>, InterestID TuteeInterest
>,td.DayOfWeekID, TuteeTimeOfDayID
>from Tutee t inner join
>TuteeInterest ti on t.TuteeID = ti.TuteeID
>inner join TuteeDay td on t.TuteeID = td.TuteeID
>inner join TuteeTime tt on t.TuteeID = tt.TuteeID
>go
>
>You now have two views that you can use in your FROM
clause. Run these
>queries to see what the views return:
>
>select * from vTutorInterestsDaysAndTimes
>select * from vTuteeInterestsDaysAndTimes
>
>Now, replace the subqueries in my original suggestion
with these views:
>
>Select distinct TutorName,TuteeName
>FROM vTutorInterestsDaysAndTimes tu
>inner join vTuteeInterestsDaysAndTimes te
>ON TutorInterest = TuteeInterest AND
>tu.DayOfWeekID = te.DayOfWeekID AND
>TutorTimeOfDayID = TuteeTimeOfDayID
>
>Does that help?
>
>Bob Barrows
>
>Mike Williams wrote:
>> I'm trying to understand that query so I can run it.
Can
>> you help me understand what exactly it is doing?
>>
>> Mike
>>
>>> -----Original Message-----
>>> Mike Williams wrote:
>>>> What I am developing is a system to match Tutors and
>>>> Tutees based on Interest, Days, and Time. I'm having
>>>> trouble matching the two based on Interest and Time
and
>>>> Days. I finally have most of the foundation laid.
Here
>>>> is what I have:
>>>>
>>>
>>>> -----------end of database layout and sample data ---
-- ----
>>>> Here is what I have for my join:
>>>> select Tutor.name TutorName, Tutee.name TuteeName
>>>> from Tutor
>>>> join TutorInterest on (Tutor.TutorID =
>>>> TutorInterest.TutorID)
>>>> join TuteeInterest on (TutorInterest.InterestID =
>>>> TuteeInterest.InterestID)
>>>> join Tutee on (Tutee.TuteeID = TuteeInterest.TuteeID)
>>>> join TutorDay on (Tutorday.TutorID = Tutor.TutorID)
>>>> join TuteeDay on (TutorDay.dayofweekID =
>>>> TuteeDay.dayofweekID)
>>>> join TutorTime on (TutorTime.TutorID = Tutor.TutorID)
>>>> join TuteeTime on (TutorTime.TimeOfDayID =
>>>> TuteeTime.TimeOfDayID)
>>>>
>>>> It seems to work, but it brings back multiples of
the same
>>>> name. What I want to know is if this is the best
way to
>>>> retrieve matches between tutors and tutees based on
>>>> interest, time, and days? Also, how could I find a
match
>>>> if the TutorID was given?
>>>>
>>> Here is what I've come up with:
>>>
>>> Select distinct TutorName,TuteeName FROM (
>>> select t.TutorID,t.Tutorname TutorName
>>> , InterestID TutorInterest
>>> ,td.DayOfWeekID, TutorTimeOfDayID
>>> from Tutor t inner join
>>> TutorInterest ti on t.TutorID = ti.TutorID
>>> inner join TutorDay td on t.TutorID = td.TutorID
>>> inner join TutorTime tt on t.TutorID = tt.TutorID) tu
>>> inner join (
>>> select t.TuteeID,t.Tuteename TuteeName
>>> , InterestID TuteeInterest
>>> ,td.DayOfWeekID, TuteeTimeOfDayID
>>> from Tutee t inner join
>>> TuteeInterest ti on t.TuteeID = ti.TuteeID
>>> inner join TuteeDay td on t.TuteeID = td.TuteeID
>>> inner join TuteeTime tt on t.TuteeID = tt.TuteeID) te
>>> ON TutorInterest = TuteeInterest AND
>>> tu.DayOfWeekID = te.DayOfWeekID AND
>>> TutorTimeOfDayID = TuteeTimeOfDayID
>>>
>>> Each subquery returns the information for the tutors
and tutees
>>> respectively. You might want to create views for each
subquery.
>>>
>>> The key is using the DISTINCT keyword to eliminate
the duplicates.
>>>
>>> Bob Barrows
>>>
>>> --
>>> Microsoft MVP - ASP/ASP.NET
>>> Please reply to the newsgroup. This email account is
my spam trap so
>>> I don't check it very often. If you must reply off-
line, then remove
>>> the "NO SPAM"
>>>
>>>
>>> .
>
>--
>Microsoft MVP - ASP/ASP.NET
>Please reply to the newsgroup. This email account is my
spam trap so I
>don't check it very often. If you must reply off-line,
then remove the
>"NO SPAM"
>
>
>.
>

Re: Tutor Project - more

Mike Williams wrote:
> That worked great, and simplified it tremendously. But I
> have a few questions for my own understanding.
>
> When I used this query,

Which query? My suggested query, or your original attempt? Give what you say
below, I will assume you are asking about your original attempt.

> I realized it did not work, it
> seems logical but why didn't it work? I noticed it
> picked a record that was not a match (an interest), but
> the join should match them all (interest, day, time),
> right?

No. I think you will find it easier to analyze this query if you do it
graphically. Grab a sheet of paper and a pencil, draw boxes to represent
each of your tables and draw the lines representing the joins in your sql
statement between the tables. This should tell you what the problem is.

SQL Enterprise Manager has a query builder tool that, while it isn't
perfect, can be helpful. It is when creating Views.


>
> Secondly, looking at the query you gave, its pretty deep,
> its like a join that joins 2 other joins, right?

Actually, it joins two subqueries.

>
> Thanks alot for your help. Now's its easy to create
> queries if given a tutorID or tutee, very easy to
> retrieve quick results.
>

You're welcome.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Tutor Project - more

I drew the boxes and lines as you specified, but didn't
immediately see the problem.



>-----Original Message-----
>Mike Williams wrote:
>> That worked great, and simplified it tremendously. But
I
>> have a few questions for my own understanding.
>>
>> When I used this query,
>
>Which query? My suggested query, or your original
attempt? Give what you say
>below, I will assume you are asking about your original
attempt.
>
>> I realized it did not work, it
>> seems logical but why didn't it work? I noticed it
>> picked a record that was not a match (an interest), but
>> the join should match them all (interest, day, time),
>> right?
>
>No. I think you will find it easier to analyze this query
if you do it
>graphically. Grab a sheet of paper and a pencil, draw
boxes to represent
>each of your tables and draw the lines representing the
joins in your sql
>statement between the tables. This should tell you what
the problem is.
>
>SQL Enterprise Manager has a query builder tool that,
while it isn't
>perfect, can be helpful. It is when creating Views.
>
>
>>
>> Secondly, looking at the query you gave, its pretty
deep,
>> its like a join that joins 2 other joins, right?
>
>Actually, it joins two subqueries.
>
>>
>> Thanks alot for your help. Now's its easy to create
>> queries if given a tutorID or tutee, very easy to
>> retrieve quick results.
>>
>
>You're welcome.
>
>Bob Barrows
>--
>Microsoft MVP - ASP/ASP.NET
>Please reply to the newsgroup. This email account is my
spam trap so I
>don't check it very often. If you must reply off-line,
then remove the
>"NO SPAM"
>
>
>.
>

Re: Tutor Project - more

This is incredibly difficult to explain via email, i've been wrestling with
it all afternoon. The closest I can come to it is that your attempt is
trying to treat twoi sources of data as if they were a single source. This
is why i quickly gave up on attempting to modify your initial attempt and
rewrote it using the subqueries.

Bob Barrows

Mike Williams wrote:
> I drew the boxes and lines as you specified, but didn't
> immediately see the problem.
>
>
>

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"