More Joins - (Double Eek) 1

[TamedTech]

Hello guys,

I spent a little time on these boards the other day asking some questions on some rather complicated joins, and fortunately Mindaugas Jack?nas was kind enough to give me a helping hand in writing them.

I’m now slightly further on in the project and have another monster relation to construct and it’s a little above my head so I’m again looking for some information. I’ll start by explaining the database I’ve currently got running, then look at explaining what I need the query to return.

To start with I have several tables (tblMember, tblMessages, tblMessageOwners, tblCampaigns, tblShedules, tblMessageCampSchedule)

I’m not sure I have the database designed in the best way to achieve this but I’m hoping I’ve at least got something close, but it’s easy enough to modify as I don’t current have any content in it.

So, now I’ll explain what I’m trying to achieve;

Each Member can own many messages, and a message can be owned by several members. A member can own many campaigns and a campaign can be owned by 1 member. Each message can belong to many campaigns and each campaign can contain many messages. Each message can have many schedules but they are dependant upon which campaign it is in.

So the final result would be that a user can have lots of messages that they can add to different campaigns, and then schedule different dates for a message in each campaign.

Now I’m looking for the query to return a list off all the messages owned by a member, and organise them into their different campaigns and attach the relevant schedule to a message dependant on its campaign.

I’m quite happy grouping the output with ColdFusion, but it is quite a large query for me to get my noggin round, so any help you guys can pass my way would be more than appreciated.

Let me know if you need any more information on this and I’ll get it to you right away.

Thanks,

Rob

 

[TamedTech]

Hello Guys,

A few days have passed and i've still not managed to solve this problem on my own after playing with plenty of inner joins and such like.

Does anyone have any advice?

Thanks,

Rob

 

[guelphdad]

Show your table layouts. add some insert statements so we can fill the tables with some sample data.

show some final data that you would expect to be output from the queries.

With that info it should be easy enough to assist you.

 

[TamedTech]

Thanks for getting back to me guealphdad, I'll try my best to illustrate what i require, and i'll try and show you my current database structure, although it might need modifying to achieve what I want.

tblMember:
-------
ID
memberName

tblMessage:
-----------
ID
messageName
messageContent
messageOwner

tblCampaign:
------------
ID
campaignName
campaignDescription
campaignOwnerID

tblCampaignMessageScheduleLink:
-------------------------------
ID
messageID
campaignID
scheduleID

tblSchedule:
------------
ID
startDate
endDate

There is a rough example of my database layout at the moment, if I need to modify it then let me know and i'll get it done, find links below for some SQL files for the tables and data for you to work with.

The basic concept of the result I want, is to pass a member ID number to the the query, and then have it return all of the messages owned by them organised into thier campaigns, then each campaign has a different message schedule, so a message can be in several campaigns and yet have a different schedule record for each one.

So i log into the system and it would say:

Rob
---

Messages:
Message 1 - blah blah blah blah
Message 2 - blah blah blah blah
Message 3 - blah blah blah blah

Campaigns:
Campaign 1 -
Message 1 - StartDate:07/06 EndDate:08/06
Message 2 - StartDate:02/06 EndDate:09/07
Campaign 2 -
Message 1 - StartDate:03/07 EndDate:04/07
Message 3 - StartDate:01/08 EndDate:02/08
Campaign 3 -

So as you can see, each message can exist in several campaigns, and have a different schedule for each campaign (note message 1 in the example).

I'm hoping that makes sense, if you need anything clarifing then please let me know and i'll get right back to you.

I'm certainly no good with database design so i'm sure i've made a complete hash of it, but i'm hoping it'll at least help illustrate what i'm trying to achieve, like i say, find the SQL links below.

http://www.tamedtechnology.net/tables/tblMember.sql

http://www.tamedtechnology.net/tables/tblMessage.sql

http://www.tamedtechnology.net/tables/tblCampaign.sql

http://www.tamedtechnology.net/tables/tblCampaignMessageScheduleLink.sql

http://www.tamedtechnology.net/tables/tblSchedule.sql

Many thanks guys, i look forward to hearing a little back from you.

Rob

 

[r937]

i would remove tblSchedule and put the start/end data right into tblCampaignMessageScheduleLink

i would remove the ID column from tblCampaignMessageScheduleLink

and since you say your design is easy enough to change at this point, i urge you to remove the "tbl" prefix from your table names -- either that, or you should be consistent and add the "col" prefix to all your column names

as for your question, if you want both a list of messages, and a list of campaign messages, those would be two separate queries



r937.com | rudy.ca

 

[TamedTech]

Hello Rudy,

Thanks for getting back to me, I'll have a play around with those concepts and see what I can come up with.

The reason i attach the 'tbl' prefix is simply because i find it easier when coding to attach prefixes to my elements, so for tables i use 'tbl' for querys i use 'q' for functions i use 'fu' for arguments I use a, it just makes more sense when i'm looking at code and need to referance things.

Thanks,

Rob

 

[r937]

so tell me, if you prefix your tables with "tbl" and your queries with "q" and so on, then why don't you prefix your columns with "col", hmmm?



r937.com | rudy.ca

 

[TamedTech]

Thanks for your help Rudy, i've managed to get that sorted now, i was just over complicating the issue. Your suggestions worked perfectly.

I'm not quite sure why i prefix or dont prefix certain things, maybe i need to start suffixing my name with 'OCD'... that would certainly explain alot. ;-)

Robocd

 

[r937]

yeah, sorry for making a big deal out of it, but that "tbl" thing just annoys the crap out of me

any place in SQL where you can put a table name, the syntax pretty much ensures that you cannot put a column name or an index name or any other kind of name except a table name, so the "tbl" prefix is really 100% redundant

as joe celko says, maybe you find it easier to read "nounParis prepIn artThe nounSpring" -- not!!

did you understand why you need two queries?

r937.com | rudy.ca

 

[TamedTech]

Well at a guess it would be because my current query that organises things into thier campains starts with SELECT * FROM tblCampaigns, which would mean that any messages not assigned to a campaign are not going to be displayed!??! I'm probably wrong.

I'm quite happy using two querys, just wanted to be fancy and do it with one.

Thanks again,

Rob

 

[r937]

before you do it with one query, sketch out what the result set would look like -- not in terms of table and column names, but in terms of the actual data in the result set rows

remember, every row of the result set must have exactly the same structure

specifically, you cannot to the following --

Messages:
Message 1 - blah blah blah blah
Message 2 - blah blah blah blah
Message 3 - blah blah blah blah

Campaigns:
Campaign 1 -
Message 1 - StartDate:07/06 EndDate:08/06
Message 2 - StartDate:02/06 EndDate:09/07
Campaign 2 -
Message 1 - StartDate:03/07 EndDate:04/07
Message 3 - StartDate:01/08 EndDate:02/08
Campaign 3 -

r937.com | rudy.ca