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

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

Join Tables For Definitions (Data Table/Definition Table)

Posted By:      Posted Date: September 22, 2010    Points: 0   Category :Sql Server


I am well versed with Join/Outer Join/Inner Join. My question is not related to normal Joining of two tables. Its little complex. Let me see if I can explain well.

There are two tables, were data is stored.

Name_Info : nvarchar(10)
Date_Info : smalldate()
Spec_Info_1 : tinyint
Spec_Info_2 : tinyint
Spec_Info_3 ; tinyint

Name_Data : nvarchar(10)
City_Data : nvarchar(10)
State_Data : nvarchar(10)

To join these two tables, I use the following query (joined on Name_Info ON Name_Data)


SELECT Name, Date=CONVERT(varchar, Date, 101), City, State,
CASE Spec_Info_1 WHEN '0' THEN 'House' WHEN '2' THEN 'Shop' WHEN '3' THEN 'Bay' ELSE '-' END AS Spec_Info_1, 
CASE Spec_Info_2 WHEN '0' THEN '10 Feet' WHEN '2' THEN '20 Feet' WHEN '3' THEN '40 Feet' ELSE '-' END AS Spec_Info_2, 
CASE Spec_Info_3 WHEN '0' THEN '2 Windows' WHEN '2' THEN '4 Windows' WHEN '3' THEN '6 Windows' ELSE '-'  END AS Spec_Info_3
FROM Table_Spec_Data LEFT OUTER JOIN Table_Name_Data On Table_Spec_Data.Name_Info=Table_Name_Data.Name_Data  

View Complete Post

More Related Resource Links

Data Flows in SSIS - Mapping Multiple source tables to Destination table **Newb question**

Hi I am new to SSIS and had a basic question. I have around 30+ tables in a db that needs to be migrated to a newer schema in the DB. The data flow task seems to be ideally suited for my requirement. My question is do I need to create 30+ different data flow tasks for this which will get executed one after the other or is there a better way to migrate large number of tables. Also how are referential constraints taken care of during such migration. Thanks and Regards, Ganesh Ranganathan
Ganesh Ranganathan
[Please mark the post as answer if it answers your question]

joining 3 tables and using join to only include records not found in other table



I have 3 tables. I would like to get the number of records in table A that do not have cooresponding co_asset_ids in TABLE C but also need
to be be found in TABLE B and where the asset_id = 3. Said another way: If TABLE A record is in TABLE B and does not find a co_asset_id of 3 as found in table C then show these records.

assetno 4 and 5 are irrelevant but added to fill out the data more.

I know I could do this getting the co_asset_id column into TABLE A and then do some joins between TABLE A and TABLE C and leave TABLE B out, which I have done in the past. I would like to finally know if this can be done by using the 3 tables together in one elegant query.

The result of this query for this example data  would be:

Table A

Table B
assetno    co_asset_id
1              11
2              22
3              33
4              44
5              55


Temporary tables in SQL Server vs. table variables

When writing T-SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have four table options: normal tables, local temporary tables, global temporary tables and table variables. I'll discuss the differences between using temporary tables in SQL Server versus table variables.

Checkboxes from data table


I have a data table that contains some values for Certifications.  This table can be updated in a different part of my application.  On the web page I am currently working on, I would like to have check boxes appear for every value in the data table.  I have been struggling with this trying to use a For Each loop and I cannot get it to work for anything.  Any suggestions? 

I am using the following Razor syntax to obtain my data (I know this works because I can display it in a WebGrid):


var db = Database.Open("MyConn");

How to get data from table based on four table



I need to know how to show top 30 records from four table

with fastest speed.. in ms sql server 2005..

hope You do the needfull


merging multiple tables in a single dataset to single table


 i have a stored procedure which returns three tables to a dataset ..... now i need to merge all three tables to a single table from d same dataset 

like dataset1 has table1 table2 and table3 .... i want all the three tabels to be merged into dataset1 itself .... instead of three diffrent tables so that i can show all three table data in a single datagrid  as a compact data and combination of 3 tables from d single dataset.....

can some1 help me please.....

Data Points: Deny Table Access to the Entity Framework Without Causing a Mutiny


Julie Lerman shows database administrators how to limit access to databases from the Entity Framework by allowing it to work only with views and stored procedures instead of tables-without impacting application code or alienating developers.

Julie Lerman

MSDN Magazine August 2010

Data Points: Windows Azure Table Storage - Not Your Father's Database


Let go of your traditional relational database thinking if you want to understand how Windows Azure Table storage works, says Julie Lerman. Luckily for you, she's done the hard work to grasp the new concepts and help you get up to speed.

Julie Lerman

MSDN Magazine July 2010

Under the Table: Visualizing Spatial Data


In this article, the author shows you three new arrivals on the SQL Server spatial visualization scene: the map control in SQL Server 2008 R2 Reporting Services (SSRS), the ESRI MapIt product, and the MapPoint Add-In for SQL Server 2008.

Bob Beauchemin

MSDN Magazine November 2009

Under the Table: How Data Access Code Affects Database Performance


In this article, the author delves into some commonly used ways of writing data access code and looks at the effect they can have on performance.

Bob Beauchemin

MSDN Magazine August 2009

Under The Table: Spatial Data Support In SQL Server 2008


New spatial data support in SQL Server 2008 opens the door to mapping and querying geometric and geographic data, allowing you to build exciting new applications.

Bob Beauchemin

MSDN Magazine February 2009

Data Points: Common Table Expressions


In T-SQL, views and derived tables serve similar purposes and have their advantages. But when working with SQL Server 2005,there's a third option--using Common Table Expressions (CTEs). John Papa explains.

John Papa

MSDN Magazine October 2007

need your help regarding table and database.. without data lose



I have a table like this in database

ItemNo ItemName Qty  

001       A               50       

002       A               20      

003       B               50  

004       C               60      

005       D               40 

006       E               90      

upto it has some 3000 rows in table..

Now what my problem is i have one more field in mydevelopment PC let us consider another column Category

I need to update this column to my client pc without losing 3000 data from table which already exist...

How to do...?

Entity Data Model and database view returning the same columns as there are in a table


When adding a stored procedure into the Entity Data Model I can select whether the procedure returns a scalar, a (new) complex type or one of the entity types I already defined. 

How do I do something similar for a view?

I mean assuming I have a view like this

CREATE VIEW FilteredFoos as SELECT Foo.* FROM Foo join ... WHERE ...

(that is a view that implements some involved filtering, but returns all columns from one table) how do I add it to the project so that I can use the entity set, but get the Foo objects, not some new FilteredFoo objects.

var foos = myDB.FilteredFoos.Include("Bar").ToList();

foreach (Foo foo in foos) { ...

Thanks, Jenda

How to create scrolling table for columns in a SharePoint Designer Data View


I have a fairly complex SharePoint Designer 2007 Data View they have added two more requirements.

1. Have the columns on the right side be able to scroll left to right as there is a large number of columns, while the first 3 columns on the left side stay locked in place.  This is only one list not two, and because of some other requirements I have it must remain to be just one list.

2. Then they also need to have scrolling up and down of the entire list with the headers locked in place.

The first requirement is more important than the second, but preferrably I need to do both.

I am new to XSL so not familar with the syntax. Is this something I can do on a custom Data View? Does anyone have any examples of how?

I have a drawing that show how it needs to look but I don't see an option here to upload a graphic.

So here is a link to my graphic example uploaded to my blog area: http://lindachapman.blogspot.com/  or just the image here: http://4.bp.blogspot.com/_aB01ue__NvQ/TGL42MOvrGI/AAAAAAAAAA4/c3xP4uCy4BU/s1600/Scrolling.png

I failed to mention that 3/4 of the columns are HTML Calculated fields wh

InfoPath and Access - two parent tables and one child table


Hi there,

I'm trying to link an InfoPath form to an Access database, I want to connect the InfoPath form to 3 tables in the Access database but InfoPath will only recognise parent-child relationships in a series (e.g. the "Company" table is the parent to the "Customer" table, which is again parent to the "Orders" table)

I need to have two parent tables and one child table, though (e.g. "Customer" is parent to "Orders", but "Inventory_Item" is also parent to "Orders"). Is there any way to establish this in InfoPath? I'm using Windows XP, InfoPath 2007 and Access 2007.

Cheers, Patrick

how to search the table name on th basis of data.


is there any way to search the table name containing a specific data  in a given database.

for example, if i have any data, say "Euro" in hand, and i know any of the table in database contain the given value, however i'm not sure which table. So if i can list out the table anyhow containing the data in hand. 

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