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


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

Joining complex SQL tables

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
 
Hi, I am new to SQL query. I would like to join two SQL queries (MS Office Query and Computer Details Quer) into one. The tables output can be found at http://rapidshare.com/files/412281201/SQL_query.zip Or, from lsoon3@gmail.com Thanks for your guidance, below are the query:  1) MS Office Query: ================ select v_Add_Remove_Programs.DisplayName0 AS [MS Office Edition] from v_Add_Remove_Programs where DisplayName0 like 'Microsoft Office Project Professional%' OR DisplayName0 like 'Microsoft Office Visio Standard%' OR DisplayName0 like'Microsoft Office Visio Professional%' OR DisplayName0 like 'Microsoft Office Enterprise%'OR DisplayName0 like 'Microsoft Office Standard%' OR DisplayName0 like 'Microsoft Office Professional%' 2) Computer Details Query: ======================= select  distinct  v_R_System.ResourceID,  v_R_System.Netbios_Name0 AS [Computer Name],  v_R_System.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup],  v_Site.SiteName as [SMS Site Name],  [Top Console User] = CASE  when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = '-1')  then 'Unknown'  Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0  End,  v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],  v_GS_OPERATING_SYSTEM.CSDVers


View Complete Post


More Related Resource Links

Joining tables from 2 SQL Server CE databases

  
Is it possible to join 2 or more tables in different SQL Server CE databases? I am currently using VB.NET 2008 and SQL Server CE 3.5.

joining two tables using dataservice

  
joining two tables using dataservice

ASP.NET insertion and joining data between tables

  
I have the following tables:1.) PRODUCT_INFORMATION2.) CUSTOMERS3.) DISTRIBUTORS4.) PRODUCT_NAME5.) CONTROLLEREach table has a primary key assigned. The customers table has an ID column and a CUSTOMER_NAME column with unique index property set to avoid any duplication. How do I insert values in the product_information table that has a foreign key column from the CUSTOMERS table if we insert a value in the CUSTOMER_NAME field of the CUSTOMERS table. I want to know how can I create a join between all the tables so that when I insert values in individual tables they are joined with a serial number column in the PRODUCT_INFORMATION  table?Please suggest!!

Joining tables from diffrent sites

  
We would like to setup some Master Lists under the Home site such as "Customer Data", and "Employee Data" and then under our multiple Sub-Sites setup Lists that have columns joined back to those tables created on the home site.  I can Join Lists which are under the same Sub-Site however I am not able to join Lists which are under the Parent Site. Is there a way to do this?   Thanks

Problems joining Partitioned Tables and using the partitioning!

  

Hi,

I am joining three tables called "SNAP", "TABLE_A" and "TABLE_B" both of these 3 tables are partitioned by DAY_CODE column of type integer.
TABLE_A is about 10 million rows ( about 2 millions per day ), TABLE_B is like 60 million rows ( about 12 million per day ) and SNAP table is just a small table saying what days i have.

So, i am trying to use a query like this:

select ....
FROM SNAP S
INNER JOIN TABLE_A A
 ON S.DAY_CODE = A.DAY_CODE
INNER JOIN TABLE_B B
 ON S.DAY_CODE = B.DAY_CODE
 AND A.ID = B.TABLE_A_ID
WHERE S.DAY_CODE = 20100923

Like you can see, i am just using the SNAP table to align the filtering on the partitioned column. The problem is that the partitioned is not selected and somehow all columns of TABLE_B at least are being scanned for what the explain plan tells me. ( It runs for more than 12 hours and no results ).

If i change the query to:

Problems joining Partitioned Tables and using the partitioning!

  

Hi,

I am joining three tables called "SNAP", "TABLE_A" and "TABLE_B" both of these 3 tables are partitioned by DAY_CODE column of type integer.
TABLE_A is about 10 million rows ( about 2 millions per day ), TABLE_B is like 60 million rows ( about 12 million per day ) and SNAP table is just a small table saying what days i have.

So, i am trying to use a query like this:

select ....
FROM SNAP S
INNER JOIN TABLE_A A
 ON S.DAY_CODE = A.DAY_CODE
INNER JOIN TABLE_B B
 ON S.DAY_CODE = B.DAY_CODE
 AND A.ID = B.TABLE_A_ID
WHERE S.DAY_CODE = 20100923

Like you can see, i am just using the SNAP table to align the filtering on the partitioned column. The problem is that the partitioned is not selected and somehow all columns of TABLE_B at least are being scanned for what the explain plan tells me. ( It runs for more than 12 hours and no results ).

If i change the query to:

Need some help with logic joining 2 tables if record doesn't excist yet

  

Hello,

I have a table "assignments" with records that are  assigned to multiple "users" by the "department" they belong to. Each user then can have it's own "status" for each assignment.

When the user  logs-in i need to show all his assignments and the status.(but status record doesn't exist yet)

When a new assignment is created I don't want to have to create a new record relating the assignment to the user. I want the relationship to be by the department the user belongs to and the department the assignment was assigned to.

Only thing i can think of is when the user logs in to load the assignments that belong to the user's department then look thru all the records. For each record loop thru the status table. If a record exist show the the status. if it doesn't then make default value and when the users clicks to view the assignment a new status record will be added.

Only thing i don't like is looping thru the status records while looping thru all the assignments.

How else can I do it? I don't see how could join the assignments table and the status table if the status record doesn't exist yet.

Thank you

tables:
[ASSIGNMENTS]
assignmentID
departmentID

Joining of Tables

  

Firstly,Ive created a table "StudentInfo" in Ms Sql .

Secondly,Ive a "ContactList.xls" in Excel 2007 which I had already imported it into MS SQL.

The problem is,how can i join the StudentInfo and ContactList into 1 table.


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

  

Hi,

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:
TableA
assetno
1
3

Table A
assetno
1
2
3


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

TABL

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.

Temporary Tables - MS SQL Server

  
Usage of temporary tables in MS SQL Server is more developer friendly and they are widely used in development. Local temporary tables are visible only in current session while global temporary tables are visible across all sessions.

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.

MVC too complex?

  

I believe lot of people will disagree with me on this one. But the MVC model sucks. The reason:

1.       Over complicated;

2.       Cannot handle complex UI;

3.       Hard to debugging as code scattered all over the place.

Using a trigger or anything else to populate two tables

  

Hi! I'm creating an application that's supposed to first add a record to table1, and then get the ID from that record to use when adding a record to table2, to be able to associate these two records with eachother.

The user gets to type in some values that goes to table1, and some values that goes to table2, but before the insert statement for table2 is executed i need the ID from the recently added record in table1. Some dude told me to use a trigger for the autopopulate purpose, but does that really work when i also need to save some values that's user input, and when those values doesn't get saved in table1?

Are there any other way to do this or can i send values to a trigger? I'm new to triggers and stored procedures, i don't have any particular knowledge of this, any help is appreciated!

 

Regards, Monsterbadboll


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.....


SqlDataSource UpdateCommand using 2 tables

  

I have two tables

Trans  with fields TransID, Date, CustomerID and some other stuff

Customer with fields CustomerID, Name, TaxId

On the screen the user only sees the fields Date and Customer Name. CustomerID is behind the scenes only.

 

I'm using SqlDataSource. Having no problems with SelectCommand. I don't know how to construct the UpdateCommand and InsertCommand.

Let's say the user changes the date, then I need to do an UPDATE.

UPDATE Trans SET Date = @Date, CustomerID = @CustomerID results in an error message and the record is not updated.

I get an error on the page that says "Sys.WebForms.PageRequestManagerServerErrorException: Input string was not in correct format".

 

I tried taking out the set for CustomerID and I still get the error on page.

 

Also, for inserting, the users will see a dropdownlist with Customer Names. I need to convert that to a CustomerID to be used in the new record being inserted in the database. I'm not sure how to do this.

 

Do I need to do something with Control Parameters?


How do I persist custom complex types in design-time

  


Let's say that I've got one custom webcontrol called MyControl.



[ParseChildren(true)]
public partial class MyControl: WebControl {
[DesignerSerializationVisibility(DesignerSerializationVisibility.Content)]
[PersistenceMode(PersistenceMode.InnerProperty)]
public IComplex Complex { get; set; }
}


The control is a very simple control but it holds an instance of a complex type that is accessable through a property. The property has the propertytype IComplex


public interface IComplex {
string Name { get; set; }
List<IComplex> Children {get;}
}


Let's say that I am able to pick one of several different types of object that derives from IComplex in design-time and assign it to the controls property "Complex" property.

Naturally I would like to persist this information to the HTML-View in a user-friendly way.

Because we don't know what type the user has picked there is no way to instansiate the type if we don't persist the name of that type. The Type could also contain more properties than the Interface the controls property is refering.


<cc1:MyControl id="MyControl1" runat="server">
<Complex> <!-

Categories: 
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