September 30, 2008 at 3:48 pm
Hi
I need to create some complex queries and I'm not sure where to start! Here is the scenario. I'll try to be brief
Users register with a website and create a profile of interests and music preferences.
Events get posted on the site and get allocated to interest and music groups.
If one of your music or interest selections match one of the criteria of a posted event it gets displayed in your intinerary.
Here is my database structure. All primary keys are of type uniqueidentifier
A Users table called aspnet_Users with a primary key UserID
An Interests table with a primary key InterestID
A User Interests link table called UserInterests with foreign keys UserID and InterestID
A Music table with a primary key MusicID
A User Music link table called UserMusic with foreign keys UserID and MusicID
An Events table called Events with a primary key EventsID which also contains a datetime column EventEnd.
An Events Music link table called EventsMusic with foreign keys EventID and MusicID
An Events Interest link table called EventsInterest with foreign keys EventID and InterestID
How do I determine what future events should be shown to what user? Have I created a suitable database model?
Any advice or direction would be appreciated
September 30, 2008 at 5:23 pm
Here is a start:
Create View vwUserInterestEvents as
Select US.UserID
, US.UserName
, IT.InterestID
, IT.InterestName
, 'EVENT' as [Type]
, EV.EventID
, EV.EventName
, EV.EventDescription
-- ... etc...
From Events EV
Join EventsInterest EI On EV.EventID = EI.EventID
Join UserInterests UI On UI.InterestID = EI.InterestID
Join Users US On US.UserID = UI.UserID
Join Interests IT On IT.InterestID = UI.InterestID
UNION ALL
Select US.UserID
, US.UserName
, IT.InterestID
, IT.InterestName
, 'MUSIC' as [Type]
, MU.MusicID
, MU.MusicName
, MU.MusicDescription
-- ... etc...
From Music MU
Join MusicInterest MI On MU.MusicID = MI.MusicID
Join UserInterests UI On UI.InterestID = EI.InterestID
Join Users US On US.UserID = UI.UserID
Join Interests IT On IT.InterestID = UI.InterestID
GO
Select * from vwUserInterestEvents
Where UserID = @MyUserID
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 1, 2008 at 9:44 am
Hi rbarryyoung
Thanks for taking the time in repying. Unfortunately I'm still having problems converting your sql to a format I need.
Here is the old query which extracts future events which haven't been replied to yet. It doesn't take into consideration the Interests and Music choices made by the user or assigned to the Events.
SELECT Address1, Address2, (SELECT COUNT(*) AS Expr1 FROM UserEvents WHERE (EventID = Events.EventID) AND (RSVP = 1)) AS Attendees, City, Comments, County, Email, EventEnd, EventID, EventStart, EventTitle, Phone, PicturePath, PostCode, Venue, Website FROM Events WHERE (EventEnd > GETDATE()) AND (NOT EXISTS (SELECT EventID, UserID, RSVP FROM UserEvents AS UserEvents_1 WHERE (EventID = Events.EventID) AND (UserID = @user-id)))
I'm an ASP.NET developer and create datasets in Visual Studio 2008. I've attached a screengrab of my Data Access Layer if it helps to visualise the problem
It's a real head scratcher and I'm running out of ideas
October 1, 2008 at 4:54 pm
You'll have to give me a clue as to what is wrong with the results from my query if you want me to change it for you.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 2, 2008 at 10:59 am
Sounds like the OP failed to initially disclose criteria that are critical to rendering a proper query. He now introduces the concept of the user replying to a given event, and thus wants to be able to filter out events the user has already replied to. Mr. Young provided a query that could easily be set up to filter out events where the user has replied, but the disclosed data structure makes no mention of where the data is that supports the existence of a reply from a given user to a given event. Given the existing structure, it would appear that an additional table, perhaps UsersReplies, with foreign keys to Users and to Events, might be useful here, and which would include a datetime field to indicate when the user replied.
Kagool, the ball is in your court. Please 1st explain what's wrong with Mr. Young's query without concern for the event reply situation. Then please explain where you're keeping the reply data. Perhaps then someone can help you create the appropriate filter to add to the solution you've already been given.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 2, 2008 at 12:31 pm
I did initially leave out the additional criteria as I thought I was muddying the waters and my problem was too complex to explain. I wasn't clear enough so my apologies if I've wasted anyones time.
Anyway, I'm almost there. My friend has kindly jumped to my rescue and provided me with a query which shows the events which have music and interest criteria that match that any of for a specified user
select distinct e.* from events as e
left join eventmusic as em on em.eventid=e.eventid
left join eventinterest as ei on ei.eventid=e.eventid
where em.musicid in (select musicid from usermusic where userid=@userid) or
ei.interestid in (select interestid from userinterests where userid=@userid)
All I need to do now is to remove the events from this query that have already been responded to. In other words if there is an entry in the UserEvents table (EventID with a corresponding UserID) these events need to be omitted from the results.
I uploaded my database structure in my second post.
I've managed to get a copy of Apress's 'Beginning SQL Queries From Novice to Professional' so hopefully soon I'll be intermediate!
October 2, 2008 at 12:44 pm
Kagool,
Thanks for clarifying. You just need to state the entire problem in your initial post, as the design of many solutions is critically dependent on the initial problem one is trying to solve. Please also note that not every responder is going to view an image in a 2nd post when there's no reason that's obvious from the posts to indicate that it would be different in any way from material already posted. That's why I never saw the table you referred to that I've highlighted in bold below:
kagool (10/2/2008)
I did initially leave out the additional criteria as I thought I was muddying the waters and my problem was too complex to explain. I wasn't clear enough so my apologies if I've wasted anyones time.Anyway, I'm almost there. My friend has kindly jumped to my rescue and provided me with a query which shows the events which have music and interest criteria that match that any of for a specified user
select distinct e.* from events as e
left join eventmusic as em on em.eventid=e.eventid
left join eventinterest as ei on ei.eventid=e.eventid
where em.musicid in (select musicid from usermusic where userid=@userid) or
ei.interestid in (select interestid from userinterests where userid=@userid)
All I need to do now is to remove the events from this query that have already been responded to. In other words if there is an entry in the UserEvents table (EventID with a corresponding UserID) these events need to be omitted from the results.
I uploaded my database structure in my second post.
I've managed to get a copy of Apress's 'Beginning SQL Queries From Novice to Professional' so hopefully soon I'll be intermediate!
You never need to feel that any given problem is too complicated. The user base here has a LOT of experience at handling pretty much everything that gets thrown our way, or at least referring someone to a good resource.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply