.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Tables column relationship

Posted By:      Posted Date: October 14, 2010    Points: 0   Category :Sql Server
In AdventureWorks sample. considering Fact "Internet Sales" and "Date" dimension, now there are 3 columns in "Internet Sales" Fact table ShipDateKey,OrderDateKey and DueDateKey which are mapped to DateKey column of Date dimension.
My question is if i drag and drop any measure from Internet Sales and use Date dimension on rows then how SSAS answers query result and which fact and dimension column relationship it use. i.e. which column amongst 3 i.e. ShipDateKey,OrderDateKey and DueDateKey it will use for response.

View Complete Post

More Related Resource Links

Relationship between a dimension - two fact tables

I have 3 fact tables and about 10 dimensions. I want to relate both "Fact_Pop1" and "Fact_pop2" to Dim_AgeGroup. But Fact_pop1 has single age group and Fact_pop2 has no single age group (5 years age group). I spent about a day on this but I could't solve it.   Fact_Pop1 Age        Race_key            Gender_key      Geography_key              Pop_size 10           White                    M                                            CA                          10000    10           White                    F        

Select column from all tables in database

I want to retrieve the name and phone columns from all the tables in my database not in systables....   Ok this works but i dont want to get it from just the test table I want to get it from all the tables that I create "USE mrpoteat SELECT name, phone FROM mrpoteat.dbo.test where name = name and phone = phone"

Rename a column in multiple Tables using sp_rename

Hi,Any work around for the below issue?Originally "LoadDate" column (see below scripts) was there both in tables DIM_Table_1 and DIM_Table_2According to change in requirement, LoadDate in both the tables to be chnged to LOAD_DATE. It has to be done through script-----------------EXEC sp_rename 'dbo.DIM_Table_1.LoadDate',     'LOAD_DATE', 'COLUMN'         -- Successfully executedEXEC sp_rename 'dbo.DIM_Table_2.LoadDate',     'LOAD_DATE', 'COLUMN'         -- Error: See below Error: The new name 'LOAD_DATE' is already in use as a COLUMN name and would cause a duplicate that is not permitted.-----------------

Discovering ODBC Tables, Columns and column Definitions

I have worked with ODBC data connections for a while.  Usually, I've setup a connection to some type of database (IBM ISeries, SQL Server, Progress, etc.) depending on the project and then I use MS Access to inspect or export the data using linked tables. I have a database that has numerous tables and columns.  In fact, I believe there are some 16k data columns in the entire database.  Furthermore, I have an excel file that gives me a list of all of these tables and a brief description of each column  but it does not have useful data types in the description.  What I would like to do is query the ODBC source and extract each table, it's columns and it's column data types. My end goal is to take the data from the spread sheet (column names and description) and insert that data into a database that also contains the correct, SQL data type in an additional field.  Is there a way to query an ODBC data source to acquire all of this information?

Relationship to One of Several Tables

I'm building a website that has several areas where comments are allowed (general topics, activities, groups, etc.). I know I can create separate tables for each comment type. But I'm thinking it would be nice to be able to create one comment table that is used for all comment types. That would mean that, for any row in the comment table, it would be linked to one of several different tables. Some possible ways of approaching this include: 1. Include multiple foreign-key columns in my comments table. Only one would be used and the rest would be NULL. (All would be indexed.) 2. Have a single column that contains a foreign-key value and another column that indicates the comment type. In this case, there would be no relationship constraints established in the database. It would simply be enforced by my code. To get comments related to, for example, a specific activity, I would use something like WHERE ComParentID = @ActivityID AND ComType = TypeActivity. I'm not sure if this approach makes much sense. Perhaps someone could comment which of these approaches sound better, or suggest a different approach altogether. Thanks.Jonathan Wood • SoftCircuits • Developer Blog

Find all tables that depend on table with FK column

I need to write a stored procedure that when passed the id of a row on a table, will query all tables that are referencing that row and move the data to another row.  I am doing this because we have 2 seperate systems that are linked by a table and sometimes things get out of whack and you have to manually move information over. I have this query that can find all the dependent tables, my question is what would I add to this to obtain the name of the column that is the foreign key on that tables.  SELECT o.name[parentTable], o2.name[childTable] FROM sys.sysobjects o INNER JOIN sys.sysforeignkeys fk ON fk.rkeyid = o.id INNER JOIN sys.sysobjects o2 ON o2.id = fk.fkeyid AND o2.id <> o.id WHERE o.name = 'tablename' I want to do this as a query because I want the procedure to be able to detect any new tables that might have been added.

SQL - Join Tables on Column Name and Row Field Value



I have two SQL tables (SQL 2005) that I need to join but am in need of some assistance. Essentially I need to join a column name with row field values.

The first table contains the following columns

  • Loan ID
  • SSN
  • Ord_SignedApp
  • Ord_Title
  • Ord_Reg
  • Ord_Name

The second table contains the following columns

  • Item
  • Ord

However, the rows in the second table for the Ord column contain the following values:

  • Ord_SignedApp
  • Ord_Title
  • Ord_Reg
  • Ord_Name

I need to join the row field value of the second table with the column name of the first table. I want to display the values of the respsective columns of table one (Ord_SignedApp, Ord_Title, Ord_Reg, Ord_Name) and place them in a new column. These values are represented as dates

Any assistance you can provide would be much appreciated.

Thank you for your help,




need to join two tables and also convert top 3 rows to column


Hi, I have two tables, CUSTOMER and PAYMENT_DETAILS. I need to extract "name" and "phone" from CUSTOMER table and TOP 2 payments for matching email from PAYMENT_DETAILS table. Kindly refer to the attached image for table structure and Result required.




PK to PK relationship between Fact and Dimension tables


Hi guys,

Is it possible to define a relationship between a Fact Table and Dimension Table using a PK to PK relationship rather than a PK FK relationship?

The reason I ask is that I'm working on building a cube, in which the tables I think I need to use as dimension tables only have matching Primary Keys in the current RDBMS, not a FK.

Is there any reason why I shouldn't use a PK to PK relationship?


compare two column from different tables


i have records in a column called CLI such that "10-7;10-8;10-9;10-10" and in another table records such that "10-7","10-8","3-2",column X.

how i can find the records that found in CLI but not found in X column.

For example,record "10-7;10-8;10-9;10-10" in CLI

is there 10-7 in column X ?

is there 10-8 in X ?

is there 10-9 in X ?

is there 10-10 in X ?

Union all in SSIS join two tables row wise or column wise

Union all in SSIS join two tables row wise or column wise

Insert NULL values into Integer column in Access 2007 tables

hi  there:

  My source data  is below and it's in CSV format

Days waited


My destination is a Number column in a 2007 access table  and the required property is set to NO

First question :

   I used a Data conversion task in DFT to convert [DT_STR] to Integer as SSIS seems to default the source as DT_STR type. However, SSIS keeps complaining that

"The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data."

I know that's because of those NULL values. how to solve this issue?


--Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

rowGuid column MUST be unique on all the tables for a same database or not ?



I've got a lot of troubles for the moment with Merge replication about data not downloaded.

But, i've got an idea. Maybe the problem is about rowguid.
I explained : after the publication is created, I saw new columns added in the tables : name rowguid,type Guid, property rowGuid.

To avoid uses a GUID value on the database for all tables publiched, for all rows, and because my Primary key on all tables is always a guid, I've added the property "rowguid" to my primary key.

BUT, my PrimaryKey is unique on the table, but not on the whole database means not on all the tables.

My question : is it a problem ? does the value of one "rowguid" must be unique on all tables of the database, or, 2 rowguid can have the same value if they are on the same publication but from 2 different tables ?



MySql for three tables relationship


Dear all,

select * from a, b, aa
WHERE b.transaction_number=a.transaction_number
            AND  b.line_number=aa.Order_detail_ID
            AND aa.po_number=a.po_number

It will withdraw all inner join mapping. How about if I want to withdraw aa where a and b not exists in aa???

Many to Many Relationship - mutliple tables


Hi all,

I am having trouble creating a many to many relationship for my cube, I just dont quite get how to set this up.

Here is the scenerio:

---> = ManytoOne

Table_E ---> Table_M ---> Table_JL ---> Table_TreeCode <--- Table_Tree

so therefore:

Table_E *-----* Table_Tree

TableJL has a treecodeId field that points to Table_TreeCode, but I would like to see Table_Tree as a dimension

Say for example I need a count of Table_E by dimension Table_Tree.

I just dont understand how to achieve that.

An Entity Relationship Diagram Example

An Entity Relationship Diagram conveys a lot of information with a very concise notation. The important part to keep in mind is to limit what you're reading using the following technique:

Choose two entities (e.g. Company and Employee)
Pick one that you're interested in (e.g. how a single Company relates to employees)
Read the notation on the second entity (e.g. the crow's feet with the O above it next to the Employee entity).

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend