i have a problem with deriving sql statement which involve a unary relationship for one of the table.
i have tables with the following attributes:
studetn table: student_email (PK), student name
section table: section_no, section_semester (composite primary key)
the above has a a M:M relationship. (in my sql database, i will have a student_section table in btw).
There is also another evaluation table which records the student_email of student evaluating another student (this will be shown as team_member email)
evaluation table: evaluation_id (PK), student_email, teammember_email, studentinput
I use the following sql to get the student name for the student who is evaluating and the students being evaluated:
select form_id, temp_tb.student_email, temp_tb.student_name, teammember_email, student.student_name as teammember_name, studentinput
from (select form_id, student.student_email, student.student_name, teammember_email, studentinput
from student inner join form_evaluation
on evaluation.student_email=student.student_email) as temp_tb
inner join student
on temp_tb.teammember_email=student.student_email
After that, i need to join to the student_section table. However because a student can be in different sections, so the student section table will have 2 entries with the same studentemail, therefore the above query (when inner join with student_section table)actually retrieve the same result twice.
after I've created a new column in a table with the "Create a new column" with custom members. How do I actually remove this newly created column without recreating the dimension ?
I have a table of products, and many products have the same name only they are different sizes. I want to only select a particular size like 20 oz, but if it does not exist in that size I want to select the next appropriate size (and which case if it does not exist i wish to select the next appropriate size and so on .. Does any one know how I can accomplish this and would be willing to help me out by posting some code, some direction ??
However, the userelationship function does not override the active relationship between Operation & Advice and so the measure is limited to Advices directly filtered by the Operation table.
If I delete the relationship between Operation and Advice, then the measure works as expected i.e. Operation indirectly filters Operation Commodity which filters Advice.
Any working example of what a ragged hierarchy should look like for the unary and custom rollup calcs to work?
I have a parent-child hierarchy that works as expected but can't manage to get the same with a flattened ragged hierarchy. Parents disappear if any of the children aren't in the fact table (even though the children are set to '~' and the parent has its own custom rollup calc).
Do I need to replicate the unary and measure formula all the way to the end in the same way i do the member name/value (so i can set ignore if same as parent)? Setting unary to null seems to work when the key doesnt exist in the fact table but the default behaviour for null is '+' so i end up getting wrong results if the key does exist!
Repro:
In words
I have an EAV style fact table. The measure name is defined in a 'dim measure' table. Some measures exist in the fact, some are manufactured using Unary ops or custom rollup formulas.
Here's a diagram of the hierarchy
I want to recreate the functionality as a flattened hierarchy (the number of levels is fixed), but can't seem to do it... Here's what I've gotten for the same hierarchy created using the two different approaches
Below are queries to recreate the tables from adventureworksdw2014. The parent child hierarchy is set up the standard way... measureparentkey is parent. set name, unary and formula as expected. Set NonLeafDataHidden for the parent attribute. I've tried various combinations for the ragged hierarchy but none work 100% of the time. I want to have a user defined hierarchy for the performance benefits.
--Fact view CREATE View dbo.FactSales AS select fis.ProductKey , fis.OrderDateKey , 1 AS MeasureKey -- salesAmount , fis.SalesAmount AS MeasureValue
I have a table with a field that has a numeric data type (15,2) and length of 9. The problem is that it won't display the actual negative sign for any values less than 0 when running a query. Any ideas? I've used Query Analyzer as well as Access.
I have a disconnected utility dimension with the following data and a waterfall->subwaterfall hierarchy. I have ignore if same as parent set on sub water fall. The unary operator is assigned to the relevant level
Opening ~ Opening ~ Price ~ Price ~ Total Composition ~ Composition L1 + Total Composition ~ Composition L2 + Total Composition ~ Composition L3 + Total Composition ~ Composition L4 + Total Composition ~ Composition L5 + Closing ~ Closing ~
I'm using MDX to set values to the members
SCOPE(Measures.Waterfall,[Dim Waterfall].[Water Fall].&[Price]); THIS=Measures.LowestPrice; END SCOPE; SCOPE(Measures.Waterfall,[Dim Waterfall].[Sub Water Fall].&[Composition L1]); THIS=Measures.[Composition L1]; END SCOPE;
[Code] ....
But when i run a simple select query
SELECT {[Measures].[Waterfall]} ON COLUMNS, [Dim Waterfall].[Water Fall].[Water Fall] ON ROWS FROM [GPA]
I only get a value for price. Total Composition is NULL. If I change my query to look at the subwaterfall level then I see values for composition L1->L5, so I know there are values there I was expecting the unary operator to aggregate my composition measures into Total Composition.
I should point out that every measure that's being assigned to my utility dim is calculated with in excess of a dozen or so steps and intermediate calcs, is this causing some kind of solve order issue?
I know that as a workaround i could probably do something like
SCOPE(Measures.Waterfall,[Dim Waterfall].[Water Fall].&[Total Composition]); THIS=SUM([Dim Waterfall].[Hierarchy].[Water Fall].&[Total Composition].Children,Measures.Waterfall); END SCOPE;
But that defeats the purpose of the unary operators
How can I set a one-to-one relationship using the Management Studio Express and SQL Server 2005 Express tblClient, CleintID (PK) tblProcess, ClientID (FK)
Hello I have need to write a query that I can pass in a bunch of filter criteria, and return 1 result....it's just ALL of the criteria must be matched and a row returned: example: Transaction table: id, reference attribute table: attributeid, attribute transactionAttribute: attributeid, transactionid Example dat Attribute table contains: 1 Red, 2 Blue, 3 Green Transaction table contains: 1 one, 2 two, 3 three transactionAttribute contains: (1,1), (1,2), (1,3), (2,3), (3,1)
If I pass in Red, Blue, Green - I need to be returned "one" only If I pass in Red - I need to be returned "three" only If I pass in Red, Green - nothing should be returned as it doesn't EXACTLY match the filter criteria
If anyone's able to help that would be wonderful! Thanks, Paul
How to create a relation between gf_game and gf_gamegenre here? gf_gamegenre is responsible for the relation between a game and it's genre(s). The relationship between gf_genre and gf_gamegenre worked. (http://img361.imageshack.us/my.php?image=relationzl9.jpg)
When I try to set a relationshop between gamegenre and game I'm getting this error: 'gf_game' table saved successfully'gf_gamegenre' table- Unable to create relationship 'FK_gf_gamegenre_gf_game'. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_gf_gamegenre_gf_game". The conflict occurred in database "gamefactor", table "dbo.gf_game", column 'gameID'. Thanks for any help!
Can anyone provided insight on how to create a one-to-one relationship between SQL tables? Every time I try to link two tables that should be one-to-one, the link says one-to-many. How can I specify one-to-one when SQL Server automatically thinks it is a one-to-many? Thanks, Kellie
Hey, I know I'm asking a stupid question but I need to get a clear response please: why using One-to-One relationship instead of meging the 2 tables in only one? thanks.
I created 2 tables with one to one relationship. if I add a record intable A, how does table B record get created? Does SQL do thisautomatically because it is one to one relationship? or do I need tocreate a trigger? if i need a trigger, how do I get the ID of newrecord to create the same ID in table B?thanks for any help.Joe Klein
Hi,Do you guys know what's wrong with a one-to-one relationship?The reason I want to make it like this is that at the very end of the chain,the set of keys is huge. I want to limit the number of columns to be thekey. i.e. the [company] table has 1 column as the key. The [employee]table will have 2 columns as the key.e,g,If I add a [sale] table to the [company]-[employee] relationship, the thirdtablewill have 3 columns as the key -- "company id", "employee id", and "saleid".(e.g.)I have a company with many employees and computers. But instead of classifyall these, I just want to call all these as an entity. A company is anentity. An employee is just another entity. etc.So, instead of a one-to-many:[company]---*[employee]---*[sale]||*[computer]I make it one-to-one.[entity]---*[entity]If I want to know the name and address of the entity "employee", I will havea 1-to-1 table [employee] to look up the information for this employeeentity.[entity]---*[entity]||[company]||[employee]||[computer]||[sale]--[color=blue]> There is no answer.> There has not been an answer.> There will not be an answer.> That IS the answer!> And I am screwed.> Deadline was due yesterday.>> There is no point to life.> THAT IS THE POINT.> And we are screwed.> We will run out of oil soon.[/color]
How do I create a one to one relationship in a SQL2005 Express database? The foreign key needs to be the same as the primary key so it can't just increment to the next number.
Hi. I get this error when i try to create a relationship in a db diagram (sql 2005) "'tblActivedir' table saved successfully 'tblClient' table - Unable to create relationship 'FK_tblClient_tblActivedir1'. Introducing FOREIGN KEY constraint 'FK_tblClient_tblActivedir1' on table 'tblClient' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint. See previous errors."
What i have is 2 tables. 1 named client 1 named activedir
In the client table the columns i want to bind with activedirtable are FR1 and DC1 I want to bind them in the ID of the activedir table (both, in different fk relationships) so that they get the id of activedir. Fr1 has an fk relationship with activedir (pk is activedir' id) and DC1 exactly the same in another fk. So i want both columns to comunicate with activedir. If p.e. activedir has 3 elements (a,b,c) when i delete element a then werever FR1 or DC1 have this element(binded to it's id) then the element will also be deleted (id of the element) from both FR1 and DC1 I don't want to set Delete and Update action to none because i want the element changed or deleted from activedir, to do the same on Fr1 or DC1 or both. Any help? Thanks.
I am trying to create a 1:1 relationship, but not primary key to primary key. In table 1 I have a uniqueidentifier as a primary key. In table 2 I have an int as the primary key and a column that takes the uniqueidentifier from table 1. Everytime I drag and drop the relationship line and link table 1 to table 2 it creates a 1:N relationship: ie. tbl1.primarykey links to tbl2.column2. So I'm not linking primary key to primary key however I still want a 1:1 relationship.
SQLServer 2005 - I have two tables. One has a field defined as a Primary Unique Key. The other table has the same field, but the Index is defined as non-Unique, non-clustered. There is no primary key defined on the second table. I want to set up a one-to-many relationship between the two, but am not allowed.
This should be simple. What am I doing incorrectly?
This is the message that i get when trying to assign keys when creating diagrams in visual express:
'tbh_Polls' table saved successfully 'tbh_PollOptions' table - Unable to create relationship 'FK_tbh_PollOptions_tbh_Polls'. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tbh_PollOptions_tbh_Polls". The conflict occurred in database "C:USERSSTICKERDOCUMENTSMY WEB SITESPERCSHARPAPP_DATAASPNETDB.MDF", table "dbo.tbh_Polls", column 'PollID'.
PollID is my primary key in tbh_Polls
And PollID is in tbh_PollOptions table
No matter what I do, I get this message, I'm Lost!
I have a fact table with 2 fields : "Dim Code 1" and "Dim Code 2" that I want to link with a Dim table. I don't want to create two dimensions Dim1 and Dim2 but only one dimension with something like :
Shirt shirt_id client_id meas_arm meas_neck meas_shoulder color_code description My question Is it possible to have a relationship linking one single table to other several one. For example i wanted to relate the field client_id from table client which is the primary to tables shirt,trousers and smoking with the client_id field which is the foreign key ?
Hi there everyone, this is my first post so go easy on me :) Basically I am trying to get my database to copy the value in the UserId (unique identifier field) from the aspnet_Users table to a foreign key UserId in a table called userclassset. I have made this field the same datatype and created a relationship between the two. Unfortunately, when I add a user using the ASP.Net configuration tool it does not automatically copy this value into my own custom table. I have noticed it is however automatically copied into the aspnet_Membership table. Any pointers on how to solve this would be great! Thanks :)
Hi, how can i make optional relationship? for example: In table A, there is column 1, column 2, column3. In table B, there is column 4, column 5 and column 6. column 1 and column 2 are primary keys for table A and table B. The relationships between table A and table B are column 2 and column 5; column3 and column 6. but optional (ie. when data exists in column 2, then column3 is null) how can i set the relationship? because one of the columns data is null each time, error always occurs.
Hello, I created some SQL 2005 tables using Microsoft SQL Server Management Studio. I need to get the script code of those tables. I was able to do that by right clicking over each table. But how can I get the code for the relationships between the tables? Can't I create relationships between two tables by using T-SQL? Thanks, Miguel
When I try to insert a record on my DB (SQL 2005 Express) I get a constraint error. This is my table setup which has been simplified to expose the problem I have: Categories TABLEint CatId PKvarchar CatName : Items TABLEint ItemId PKvarchar ItemName : X_Items_Categoriesint CatIdint ItemId So basically I have a one-to-many relationship between Items and Categories, in other words each item is associated to one or more categories and this association is done via the X_Items_Categories cross table. On this cross table I set two constraints:
The CatId of each entry in the cross table (X_Items_Categories) must exist in the Category table, and
Hi there, I just set up my AppData and i'm trying to connect the Membership to a tbl_Profile that i created.I notice there's an application_id and a user_id which are uniqueidentifiers. I've been trying create a relationship to tbl_Profile's user_id column but it won't let me saying it's not compatible. Am i suppose to set tbl_Profile's user_id column to int or uniqueidentifier? I've tried both and it won't let me... THanks!
HiI have 2 tables one is called QuickLinks and another called QuickLinkItemsQuickLinksQuickLinkID<PK> UserID QuickLinkName-------------------------- ----------- ------------------------1 111-111-111-111 QuickLink12 111-111-111-111 QuickLink23 222-222-222-222 QuickLink1 QuickLinkItemsQuickLinkID <FK> CharacterName CharacterImagePath---------------------- ------------------------------- ---------------------------------------- 1 a path11 b path22 d path32 e path43 a path14 b path 2 So thats how I see the data will look like in both of these tables the problem however I don't know how to write this into Ado.net code. Like I have made a PK to FK relationship on QuickLinkID(through the relationship builder thing). I am just not sure how to I do this. For the sake of an example say you have 5 text boxes 2 text boxes for inserting the character names and 2 text boxes for inserting the CharacterImagePath. Then one for the Name of the QuickLink. So how would this be done? Like if it was one table it would be just some Insert commands. Like if this was all one table I would first get the userID from the asp membership table then grab all the values from the textBoxes and insert them where they go to and the QuickLinkID would get filled automatically. The thing that throws me off is the QuickLinkID since it needs to be the same ID as in the QuickLink table. So do I have to first insert some values into the QuickLink table then do a select on it and grab the ID then finally continue with the inserts Into the QuickLinkItems table? Or do I have to join these tables together?Thanks
hi there, after a lot of fussing about i found it would be a lot easier to use an SQLDS and a gridview to display my data rather than code it. However i cant for the life of me work out how to replace some of the numbers (IDs) with their accompanying names. so i have a QTY which is an INT and a Location1 which is an INT in tbl_stock_location and in tbl_locations i have the appropiate 1 = shop etc this is what i came up with for my string SELECT tbl_stock_part_multi_location.stock_part_multi_location_ID, tbl_stock_part_multi_location.stock_ID, tbl_stock_part_multi_location.Description, tbl_stock_part_multi_location.Qty1, tbl_stock_part_multi_location.Location1, tbl_stock_part_multi_location.Qty2, tbl_stock_part_multi_location.Location2, tbl_stock_part_multi_location.Qty3, tbl_stock_part_multi_location.Location3, tbl_stock_part_multi_location.Qty4, tbl_stock_part_multi_location.Location4, tbl_stock_part_multi_location.Qty5, tbl_stock_part_multi_location.Location5 FROM tbl_stock_part_multi_location INNER JOIN tbl_location ON tbl_stock_part_multi_location.Location1 = tbl_location.Location_ID AND tbl_stock_part_multi_location.Location2 = tbl_location.Location_ID AND tbl_stock_part_multi_location.Location3 = tbl_location.Location_ID AND tbl_stock_part_multi_location.Location4 = tbl_location.Location_ID AND tbl_stock_part_multi_location.Location5 = tbl_location.Location_ID WHERE (tbl_stock_part_multi_location.stock_ID = @stock_ID)"
I used the query builder for this but it just dosnt seem to work and throws an error at me. Any help would be great!
