.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

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

Posted By:      Posted Date: October 27, 2010    Points: 0   Category :ASP.Net
 

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


View Complete Post


More Related Resource Links

Joining complex SQL tables

  
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

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:

Relation between tables doesn't work

  

I have such entities


Then I tried to retrieve node type from Node this way - Nodes node = Entities.Nodes.First(); string nodetype = node.NodeTypes.Name; but for some reason node.NodeTypes equalls null despite fact that there is some records in NodeTypes table. Any ideas?


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:

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.


Updating a table - or add when the record doesn't exist

  

Hi,

I'd like to be able to update a table from a VB application without the table being part of the project.

I know how to insert a record:

 

Dim connectionstring As String = My.Settings.AdminCentreConnectionString

Dim eSQL As String =

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

Cannot insert new record into multiple tables thru. sql 2005 View

  
I have a sql server 2005 database containing several tables: Orders, Shipping, and Planning.  They are joined together in the sql view V_Main.  I also have a ms access 2007 mdb that links to V_Main via odbc.  I created an access form using the view V_Main as the record source.  I cannot insert a new record into each of the 3 base tables using V_Main as the record source.  A new record is inserted into only one table.
I did find a suggestion in another forum that suggested copying the sql that creates the view into the record source, rather than the name of the view.  This does work, but adds another place to be updated manually should and other table be added to the view in the future.
I did verify that all tables have primary keys.
How can I add a new record for each of the 3 base tables using the view as the record source?  What is the code that could be placed in vb of the form?
Any suggestions would be helpful.
Thank you.

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.

GridView inline Master/Detail record display

  
This code drop is part of a smash and grab series. If you're in a rush, you can just grab this code and insert it into your application, no understanding is required. When you have some time (ya, right) you may want to review the source code.

Logic behind N -Tier Application Development

  
In the arena of application development, developers all over the world use various programming languages, technical processes, and technical tools. While using number of technical & non technical elements the processes or methods become complex, for simplification of the application development process, developers use various techniques such as dividing the application architecture into layers or parts. In the professional & technical language this separation or division process is called N-Tier Application Development process, where "N" stands for number and "tire" stands for layers or parts.

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