Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Mar 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question connecting three tables with left join and ordinary join

    I need a little help.
    I have 3 Mysql tables:
    Week (with columns day and hour)
    Activity (with columns day, hour, activityid and ac_text)
    Person (with columns name and activityid)

    I would like to create a scheme showing the activities during a week sorted on days and hours. If I ignore the person table I can fix it with the statement:
    Select Ö. From week left join activity on (week.day = activity.day) and (week.hour = activity.hour) order by day, hour
    I can then make a loop (I am usin asp.net) that writes the activities.
    My problem is when I try to combine the persons to the activtities in an given hour. How do I do that ? (activity.activityid = person.activityid).

    I have a little extra question. When I make the join above and print the result (day, time and activity) there isnít any output if no activity matches a given day and hour. How do I do when I always want to print day and hour and add activity where such exist.

    I hope I can get some help.

  • #2
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    My problem is when I try to combine the persons to the activtities in an given hour. How do I do that ?
    That depends on how you want this displayed and if there is a one to one or a one to many relationship in the keys. Can more then one person have that activity id?

    I can then make a loop (I am usin asp.net)
    .NET? Repeater is your friend if GridView isn't doing it for you. Usually there is no need for looping unless you're doing old school, classic asp response.write stuff. Why are you looping? And what are you looping? Are you using a DataSet, an SqlDataReader, or ...?

    How do I do when I always want to print day and hour and add activity where such exist.
    Use Repeater and OnItemDataBound check values and other interesting jazz. It's not pretty, but if you need help I might be able to use Northwind as a sample.
    You said MySQL? With .NET?
    My example would be SQL Server but they should be similar.

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #3
    New to the CF scene
    Join Date
    Mar 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    YES - I am very interested in getting som help.
    I am using Mysql in ASP.NET 2.0.

    My needs is a little more complex than I described in my first post. This is what I need:
    I have three tables:
    Week (with columns thedate, day and hour)
    Activity (with columns day, hour, activityid and ac_text)
    Person (with columns name and activityid)

    For a given person I would like to see his/her activities in a given week. And the layout should be a timetable like this.
    Timetable Peter 2007-03-19 - 2007-03-26
    Monday Tuesday .....
    8-9 Meeting
    9-10 consulent

    There might be empty positions in the timetable.

    How do I do that ?
    If I could get a solution with repeater and gridview it would be perfect. I have tried without succes.

    Thanks.

  • #4
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    I'm not sure I understand your database tables.
    Is that architecture set in stone?
    Why aren't you just using real timestamps/datetimes (whatever mySQL uses) instead of that whole hour day thing? You could ditch the "week" table altogether, I think, if you did that, and make your life a lot easier.

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/


  •  

    Posting Permissions

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