Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
settinUpShop
Starting Member
28 Posts |
 Posted - 2003-11-10 : 00:12:52
|
| K, here's my situation. I've created an ActivityLog table to keep track of user activity within an asp based content management utility i've created. The table has the following columns:ActivityLogIDUserInfoIDActivity_TypeRecord_PKnameRecord_PKvalueNotesNotified_EmailsActivity_DateTimeI use this table to create a log of activity when users create or edit Page Content, Related Links, and Upcoming Events. (which are represented by three different tables: PageInfo, RelatedLinks, and UpcomingEvents.) The Record_PKname and the Record_PKvalue was my way of recording which type of data was being created or edited in each particular action. So if someone was creating a page, then Record_PKname would be PageInfo and Record_PKvalue would be "x", whatever the primary key value was for the record being created. I figured this is all the information I need to get whatever information I need for this activity. But now I'm trying to build a stored procedure to return all this information and I can't figure out how to do it all in one record set? Is it possible to create a sub query or user defined function in a select statement's select list? I was reading up on SELECTS in books online and it doesn't seem to be possible.Or am I approaching this the wrong way? Maybe I should just bag the whole PKname/value thing and just replicate the data from those other tables into the ActivityLog table. So for example I could take the Page_Headline value from the PageInfo table and replicate it in the ActivityLog table, but then that data will immideately be stale and outdated, where as if I somehow could create a relationship between the ActivtyLog table and the table with the given PK for that log item, I could return up to date information. The catch is that this relationship could be between ActivityLog table and the PageInfo table or the RelatedLinks table or the UpcomingEvents table, depending on what type of activity it is detailing.Does that all make sense? Any suggestions? |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-11-10 : 01:10:39
|
What's the structure of PageInfo, RelatedLinks and UpcomingEvents like? You might be able to return all of them in one recordset if they have reasonably similar structures. The keyword here is UNION, the following query might give you a few ideas:SELECT ActivityType, Notes, Headline, Description FROM PageInfo INNER JOIN ActivityLog ON PageInfoID = Record_PKvalue AND Record_PKname = 'PageInfo'UNION ALLSELECT ActivityType, Notes, Headline, Description FROM RelatedLinks INNER JOIN ActivityLog ON RelatedLinkID = Record_PKvalue AND Record_PKname = 'RelatedLinks'UNION ALLSELECT ActivityType, Notes, Headline, Description FROM UpcomingEvents INNER JOIN ActivityLog ON UpcomingEventID = Record_PKvalue AND Record_PKname = 'UpcomingEvents' Duplicating data across the tables is an option but not a very good one, as it breaks the basic laws of normalization, and as you have noted, you'd have trouble keeping up with the updates, deletes, etc.Owais
Where there's a will, I want to be in it. |
 |
|
|
settinUpShop
Starting Member
28 Posts |
Posted - 2003-11-10 : 01:21:21
|
| Oh cool. yeah I think union will do it. My structure for the three tables:PageInfo:PageInfoIDPage_FilenamePage_TitlePage_ContentPage_Headline <-- would like to show thisMeta_DescriptionPageSectionID (FK)Sort_OrderLink_TextPage_Status <-- would like to show thisPage_AsideSidenav_FeatureForm_IncludeParentPageIDUpcomingEvents:UpcomingEventsIDEvent_Title <-- would like to show thisEvent_LocationEvent_DateBlurb_TextSort_OrderEvent_Status <-- would like to show thisExpire_Date <-- would like to show thisBoothLink_URLLink_TextAnnouncements:AnnouncementsIDBlurb_Text <-- would like to show thisLink_URL <-- would like to show thisPosted_DateSort_OrderLink_Status <-- would like to show thisAnnounceCatID (FK)Do I need to have the same number of columns returned in each select if I'm going to use a union between them as you suggest? |
|
|
|
settinUpShop
Starting Member
28 Posts |
Posted - 2003-11-10 : 01:25:50
|
| One other question, if I use union can I sort the collective results of that union by common columns? like UserInfoID, or Record_PKname, or Activity_DateTime? all of these columns are in the ActivityLog table. |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-11-10 : 03:28:51
|
Well, you don't really have to have the same number of columns retreived from each table, however each query needs to return the same number of columns. You can manage that by having dummy or placeholder columns for tables that don't have enough columns.SELECT Title, Description, 'N/A' AS SomeColumn, AnotherColumn FROM TableAUNION ALLSELECT Title, Description, SomeColumn, 'N/A' FROM TableB This works if you can adequately handle the differing values at the client end as well. You should generally avoid mixing datatypes in a column, as that could throw type mismatch errors at the client, if you are going to be processing those values there.For sorting the collective results, you can use another neat trick: derived tables. You basically wrap the resultset into a virtual table and sort the results just as you would with a normal table.SELECT UserInfoID, Activity_Datetime, Headline, Description, blah, blah...FROM( --Insert your UNION queries here) Subquery1ORDER BY UserInfoID, Activity_Datetime Owais
Where there's a will, I want to be in it. |
|
|
|
settinUpShop
Starting Member
28 Posts |
Posted - 2003-11-10 : 10:00:40
|
| Thanks for your help OwaisWhat do you mean by Virtual table? do you mean using the "Create Table..." syntax to create a temporary table? Or are you referring to "Create view..." syntax? Or are you referring to a table datatype? I've never used these things before, so I'm a bit confused about how these three things are different, and in what context they should be used. |
|
|
|
settinUpShop
Starting Member
28 Posts |
Posted - 2003-11-10 : 11:00:08
|
| Hi Owais,I used the syntax you supplied and it worked. I created an If decision tree so that I can sort by various columns using the virtual table as you called it.I was just reading up on sub queries in Books Online to understand how this all works. I don't understand why after the subquery, you wrote "Subquery1"? They don't use that in any examples I found in books online. I tried the query without it and it gave me an error. Is technically what we're doing here just using a subquery? and is that in essence a kind of virtual table? |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-11-10 : 14:56:23
|
Uh there goes my attempt at simplifying a concept  . What I was refering to as a virtual table is just a derived table: a contrived query with expressions and aggregates can be wrapped in two simple brackets to make it behave like a normal table. The "Subquery1" was there for a good reason: you can't use derived tables without an alias, you can use any name you like. If you search this site for derived tables and subqueries you will find some really interesting and clever applications for them (as with everything else on this site  ).Owais
Where there's a will, I want to be in it. |
|
|
|
|
|
|
|
|
I'm stuck...