...

View Full Version : connecting three tables with left join and ordinary join



sixtus
03-16-2007, 08:03 PM
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.

nikkiH
03-16-2007, 10:41 PM
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.

sixtus
03-17-2007, 09:28 AM
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.

nikkiH
03-20-2007, 02:31 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum