Professional Web Applications Themes

Tutor Project - more - ASP Database

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

  1. #1

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

  2. #2

    Default 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 


    same 
    Mike Guest

  3. #3

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

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

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

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


    Bob Guest

  4. #4

    Default Re: Tutor Project - more

    Mike Williams wrote: 
    <snip> 
    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"


    Bob Guest

  5. #5

    Default 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
     [/ref]
    Here 
    ><snip> [/ref]
    ---- [/ref]
    same [/ref]
    to [/ref]
    match 
    >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[/ref]
    tutees 
    subquery. 
    duplicates. 
    spam trap so I 
    then remove the 
    Mike Guest

  6. #6

    Default Re: Tutor Project - more

    I think it will be easier to understand if you create views instead of using
    the subqueries. Fire up Query yzer 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: 
    >> <snip> 
    >> 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"
    >>
    >>
    >> .[/ref][/ref]

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


    Bob Guest

  7. #7

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


     
    views instead of using 
    script: 
    clause. Run these 
    with these views: [/ref]
    Can [/ref][/ref]
    and [/ref][/ref]
    Here [/ref][/ref]
    -- ---- [/ref][/ref]
    the same [/ref][/ref]
    way to [/ref][/ref]
    match [/ref][/ref]
    and tutees [/ref][/ref]
    subquery. [/ref][/ref]
    the duplicates. [/ref][/ref]
    my spam trap so [/ref][/ref]
    line, then remove [/ref]
    >
    >--
    >Microsoft MVP - ASP/ASP.NET
    >Please reply to the newsgroup. This email account is my[/ref]
    spam trap so I 
    then remove the 
    Mike Guest

  8. #8

    Default Re: Tutor Project - more

    Mike Williams wrote: 

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

    No. I think you will find it easier to yze 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.

     

    Actually, it joins two subqueries.
     

    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"


    Bob Guest

  9. #9

    Default Re: Tutor Project - more

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


     [/ref]

    >
    >Which query? My suggested query, or your original[/ref]
    attempt? Give what you say 
    attempt. 
    >
    >No. I think you will find it easier to yze this query[/ref]
    if you do it 
    boxes to represent 
    joins in your sql 
    the problem is. 
    while it isn't [/ref]
    deep, 
    >
    >Actually, it joins two subqueries.

    >
    >You're welcome.
    >
    >Bob Barrows
    >--
    >Microsoft MVP - ASP/ASP.NET
    >Please reply to the newsgroup. This email account is my[/ref]
    spam trap so I 
    then remove the 
    Mike Guest

  10. #10

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

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


    Bob Guest

Similar Threads

  1. Anyone know where to find an Action Script 3.0 tutor?
    By sammy545 in forum Macromedia Flex General Discussion
    Replies: 1
    Last Post: May 9th, 11:25 PM
  2. Need Flash ASP Data Tutor
    By redtiger in forum Macromedia Flash Data Integration
    Replies: 0
    Last Post: March 6th, 08:21 AM
  3. Tutor and Tutees website system
    By Mike in forum ASP Database
    Replies: 2
    Last Post: October 1st, 09:25 PM
  4. Windows XP Tutor
    By RebeccaB in forum Windows XP/2000/ME
    Replies: 0
    Last Post: July 6th, 02:03 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