.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

Optional Inner Join

Posted By:      Posted Date: October 28, 2010    Points: 0   Category :Sql Server

I have three temp tables. The following code joins the data from the tables together:


Select …

From myTable G

inner join #Temp1 T1 on G.[Global Dimension 1 Code] between T1.MinVal and coalesce(T1.MaxVal, T1.MinVal)

inner join #Temp2 T2 on G.[Global Dimension 2 Code] between T2.MinVal and coalesce(T2.MaxVal, T2.MinVal)

inner join #Temp3 T3 on G.[Fund No_] between T3.MinVal and coalesce(T3.MaxVal, T3.MinVal)

View Complete Post

More Related Resource Links

More Versioning Fun With Optional Arguments

In my last blog post , I covered some challenges with versioning methods that differ only by optional parameters. If you haven't read it, go read it. If I do say so myself, it's kind of interesting. ;) In this post, I want to cover another very subtle versioning issue with using optional parameters. At the very end of that last post, I made the following comment. By the way, you can add overloads that have additional required parameters. So in this way, you are in the same boat as before. However, this can lead to subtle bugs. Let's walk through a scenario. Imagine that some class library has the following method in version 1.0. public static void Foo( string s1, string s2, string s3 = "v1" ) { Console.WriteLine( "version 1"...(read more)

Versioning Issues With Optional Arguments

One nice new feature introduced in C# 4 is support for named and optional arguments . While these two features are often discussed together, they really are orthogonal concepts. Let's look at a quick example of these two concepts at work. Suppose we have a class with one method having the following signature. // v1 public static void Redirect( string url, string protocol = "http" ); This hypothetical library contains a single method that takes in two parameters, a required string url and an optional string protocol . The following shows the six possible ways this method can be called. HttpHelpers.Redirect( "http://haacked.com/" ); HttpHelpers.Redirect(url: "http://haacked.com/" ); HttpHelpers.Redirect( "http...(read more)

Optional named parameters work pretty well

Rob has found a use for dynamic: http://blog.wekeroad.com/2010/08/06/flexible-parameters-with-csharp Yay! Let's celebrate! Well, I was a little puzzled because I don't think it quite adds up in the specific example he chose (although please see no aggressiveness here: Rob's a friend; peace!). The idea is to have the same flexibility that a dynamic language can offer in terms of evolution of an API. Here's his original Ruby example: def my_method(args)   thing_one = args["thing1"]   thing_two = args["thing2"] end my_method :thing1 => "value", :thing2 = Time.now The idea, which is quite common in dynamic languages, is that instead of passing a list of predetermined parameters in a specific order, you...(read more)

Join Two Tables and Prepare Report



            I have a select query which is executing well. Now, I want to add one more field to that query. That field is not in the current query table, It is in the another table.

How do I join those two tables and get that field value in the existing select query.?


Linq join fields for return data


  how would I join fields together?

           return (from c in storedb.Product_Categories

                         where c.Category_Name.Contains(searchText)
                         orderby c.Category_Name
                         select new {
                                    c.Category_Key && " ;" && c.Category_Name // HOW CAN I DO THIS.....

How to join two sharepoint list in c# by CAML query?

Hii All,
   I am using wss 3.0 and i have one site and site has 4 to 5 lists. lists are interconnected with lookup column and same column. I need to display some column fron one list and some from another.. i need to join that lists by where condition like project =project..

So how can i join??

Thank You

Cannot properly join lists in SPD 2007


Ok, I have been reading all sorts of posts and such for about a week and still have no come up with any concrete answers.


I cannot properly join lists in SP 07. I say properly because I never get the option to select a field to associate one with the other. 

I select "Create new linked data source" from Data Source Library; Pane opens; Left Click "Configure Linked Data Source"; Add 4 to 5 lists from "Current Site"; Select Next; Select Join option but Next is greyed out. Is this supposed to be greyed out because they are lists? Can lists be joined? I see alot of examples on joining databases and excel pages but not too many on lists.

If I select next from the last point, open the linked sources and select my newly created linked source and click to show data, I can see all of the information in there. Problem is I don't believe the lists are really joined by anything since I never received an option for it. 

I also have been reading up on joined subviews but I never receive an option for  joined subview. Is the joined subview the way to go for lists? If so how?


I know these boards are fairly active and any information or help would be greatly appreciated.

insert into in combination with inner join


Hi All,

I have my data split in 3 tables for cascading dropdownlists.

How can I make an update statement of this select statement?

SELECT distinct BIER.Naam as naam1, BIER_L2.L2_Naam as naam2, BIER_L3.L3_Naam as naam3
	from BIER, BIER_L2, BIER_L3
	where BIER_L2.L2_ID = BIER.L2_ID and BIER_L2.L3_ID = BIER_L3.L3_ID 



Join 2 flat file data flows - retain unmatched rows

I have two data flows from two separate flat files. They may contain matching IDs (account number), in this case specific data from each flow should be used to create one row. When there is no match, the rows would stand on their own. At the end of the flow, I need both flows combined into one flow, with one record for each key record (account number). If I were able to use a look-up, I could easily union the no-match data flow back into the match data flow and have the desired result. I cannot use a look-up, since the source is flat files, but this is exactly the functionality I am trying to achieve. Solutions I want to avoid: staging tables, and cache transformations. Any ideas are appreciated.

repeating merge joins using full outer join - should be possible without sort, but cannot get the co

Hello,   I am trying to combine several source tables with Merge Join.  For the Moment I have three source tables all already sorted in the source query and the is sorted property with the correct keys set. (its an aggregate query with group by and sort by the key columns) Then I do a merge join between two of the tables - with a full outer join. Then I just want to do another merge join with the output of the next result. but as the merge join do not combine the key columns into one column I need to add a derived column which substitute null values on the left side of the join with values from the right side of the join. The second merge then says only the left columns of the first merge join is sorted, but not the comined columns. If i Replace with a derived column the left side primary key columsn with the combinded key columns the next merge join says the input is not sorrted? But the output must be sorted. So how do I get the next merge join working without the ssis sort transformation? the data is sorted, but the component does not recognize the sorted data? I do not want to use (can use) the sort because there are flowing >= 500 Mio. records through the pipeline. Any help appreciated.   Hanneshttp://www.hmayer.net/

Optional Parameters - Not taking values when supplied

All, I have a report that takes 2 optional parameters, one int and one datetime. My data tab is set up as follows: CommandType = Text, Parameters has Parameters.Value etc. for both. Report Parameters are allowed NULL value, Default Value is NULL, datatypes are Integer and Datetime as well. This only runs when the parameters are not supplied values, when I enter a date, it does not take it into account, same for the int value. I have run the proc manually to verify that the query is set up correctly, it seems like the values are not being passed? Any help debugging this would be helpful. Thanks.

select max record to join another table sybase

select a.pono,(select (user) from user where userid=a.userid having date=max(date)) as user from a inner join b on a.no=b.no  in the result , i have selected the same id and retrieve two records every thing are same except the date how can i select the record out of two record which date is max date as the where Clasuse to select correct user poid    date                name 1        12/08/2010      Mary 1        20/08/2010      Peter   now i would like to select name which id=1 and date is max and then use the name to join another table because name is foreign key  

optional output parameter in CLR Stored Procedure

I'm trying to transpose a Procedure that sends mail via CDO.Message and Ole Automation to a .Net CLR Procedure. this procedure must have the same parameters in the same order as the original to maintain compatibility with everything that already uses it. the declaration goes as follow   <Microsoft.SqlServer.Server.SqlProcedure(name:="SP_ENVIAEMAIL")> _<br/> Public Shared Sub ENVIAEMAIL(<SqlFacet(MaxSize:=100)> Optional ByVal De As String = "", <SqlFacet(MaxSize:=100)> Optional ByVal Para As String = "", _<br/> <SqlFacet(MaxSize:=100)> Optional ByVal Copia As String = "", <SqlFacet(MaxSize:=100)> Optional ByVal Oculta As String = "", _<br/> <SqlFacet(MaxSize:=100)> Optional ByVal Assunto As String = "", <SqlFacet(MaxSize:=-1)> Optional ByVal Mensagem As String = "", _<br/> <SqlFacet(MaxSize:=-1)> Optional ByVal Anexo As String = "", _<br/> <strong><SqlFacet(MaxSize:=1000)> <Out()> Optional ByVal Retorno As String = ""</strong> , _<br/> <SqlFacet(MaxSize:=100)> Optional ByVal Smtp As String = "", <SqlFacet(MaxSize:=100)> Optional ByVal Usuario As String = "",

problem with inner join

Hi, I'm trying to create an sql query with multiple inner joins, but I can't manage this. I have 3 tables (bookings, accountplan, costs), where the data come from. In accountplan are hold the accounts, in costs the cost codes and their names, in bookings the booked events. Based on a booking event, I need the account name, the name of the cost code and all data from bookings (I need the cred account name too, but I can't figure out yet, how I can get this) I tried this: "SELECT bookings.*, accountplan.accountname, costs.costname FROM bookings INNER JOIN accountplan ON bookings.debaccount=accountplan.account INNER JOIN costs ON bookings.costcode=costs.costcode" It is here a where clause too, but I tested it, it's ok. The above query results no rows. If I remove the costs table and the inner join with it, it works. What am I missing?

Need help on populating data on a datagrid using SQL Join

I have a table called Leaves. Fields are,LeaveIDPersonIDActingPersonIDIn Person's table, I have,PersonalIDEmployeeCodePersonNameNow, in the datagrid, I need to show,PersonEmployeeCodePersonNameActingPersonEmployeeCodeActingPersonNameNow, I know how to write the SQL Join to get the data populated. But, the problem is, in some cases, there won't be any acting person. In which case, I need to display data, PersonEmployeeCode = 0001 PersonName = John ActingPersonEmployeeCode = - ActingPersonName = No acting Person Now, I am stuck here. How do I do a conditional join statement?Please help! RegardsKush

Cross list query for two sharepoint lists and a join between the fields

Hello All, I have a requirement where i need to query the data from two  sharepoint lists  on a same site by doing a join between those lists and get the result into datatable.I am doing this using sharepoint object model.Is there any possibility to do this. Any pointers in the right direction are greatly appreciated. Thank you Smith   smith


Hello EveryOne,               I am making a monthly attendance details of all employees who is working in my company. I am using sql server management studio 2005.       My table is in this format. staffcode   name                                   date            Intime   Outtime Duration                         1               Amit                                  01-08-2010    00:00    00:00    00:00 1              Amit                                   02-08-2010    09:52    20:01    10:09 1&n
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