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


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

Merge Join: Full Outer Join - keep key values in case of no-match

Posted By:      Posted Date: September 08, 2010    Points: 0   Category :Sql Server
 
I'm using the Merge Join to join several different incoming flows into one flow.  I've configured the joins to use a Full Outer Join because I need all records from all sources. In the case of a no-match, I want the component to keep the values of join key fields instead of setting them to NULL.  How can I achieve that?  (Activating the checkbox doesn't help, because that adds a new field to the output instead of re-using the existing one.)


View Complete Post


More Related Resource Links

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/

Need help with mis-match full outer join

  

Hi, I'm sql 2005.  I have a sp that has 3 parts.  1st part selects from database A into a #tempA, 2nd part selects into a #tempB.  These 2 tables have the 6 common fields and only one field that is different so each table has 7 columns.
Part 3 of this sp full out join these 2 temp tables on the first 4 common fields and only include row if either  col5 and col6 subtraction difference is not equal to 0(meaning they should have the same value for both table of col5 and col6).  Select these rows into a 3rd temp table. then return the results of it.
The problem I have now is my 3rd table would record rows that should be a match but because of select order from each table ends uip with mis-matched the rows.
SELECT A.COL1,A.COL2,A.COL3,A.COL4, A.COL5,B.COL5,A.COL6,B.COL6,(A.COL5-B.COL5)DiffCol5,(A.COL6-B.COL6)DiffCol6
INTO #TEMP3
FROM #TEMP1 FULL OUTER INNER JOIN #TEMP2
ON A.COL1=B.COL1 AND A.COL2 = B.COL2 AND A.COL3=B.COL3 AND A.COL4=B.COL4
WHERE (A.COL5-B.COL5)<> 0
OR (A.COL6-B.COL6)<> 0

 

#tempA
  col1        col2        col3        col4        col5        col6
                     

Inline table valued function, full outer join and cross apply

  
Good afternoon, I'm experiencing quite strange issue with our T-SQL code. We have inline table-valued function which is using full outer join on 1 = 1. The reason why we do it this way is - we need to combine one row from 2 different result sets either of them can have either 0 or 1 row. The strange thing, that if we have the situation when both row-sets are empty, function does not return the row (as expected). Although, select which uses this function with cross apply "uses" the result from the previous row. Below is the simplified example. First select with ID = 2 predicate does not return any rows. Although if I remove this filter, it re-uses Value2 from the previous row. create FUNCTION dbo.Func(@Id int) RETURNS TABLE AS RETURN ( with CTE1(Value1) as ( select 'Value1' where @ID = 1 ) ,CTE2(Value2) as ( select 'Value2' from CTE1 ) select CTE1.Value1, CTE2.Value2 from CTE1 full outer join CTE2 on 1 = 1 ) go select * from dbo.Func(2) go create table dbo.TestTable ( ID int not null ) go insert into dbo.TestTable values(1), (2) go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f where t.ID = 2 go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f order by t.id go Our SQL version is: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) &nb

Full join and Null values

  

Hello

I have two tables named tb1 and tb2:

tb1                                                   

id   name  sold   type

1         a     5        1

2         b     10      1

3         c      2       2

 

tb2                                                   

id   name     type

1       &nb

Left Outer Join fails to return all records

  
I have a table tblDrop that contains 1067 records for 8/31/2010 and a table tblSoftcount that has 739 records, some with bad serial numbers. I need to match all of the records in tblDrop with those in tblSoftcount. I have a query using Left outer join that has worked for the duration of the application, but has recently stopped returing the full set of records from tblDrop. We have not changed the SQL versions on the server (compatibility level 2005). Here is the query: SELECT   v.SlotNum_ID, s.SlotNum_ID, v.PTNumber, s.PTNumber, v.DropBillAmount AS [Drop], v.ReportDate, v.DateValue     FROM tbl_Drop v LEFT OUTER JOIN tblSoftCount s ON v.SlotNum_ID = s.SlotNum_ID AND v.ReportDate = s.ReportDate WHERE   s.ReportDate = '8/31/2010' AND v.DropBillAmount <> 0 AND s.Slotnum_ID IS NULL;  

why am i getting a hash match join?

  
I am joining two large tables, 100+million rows each. They each have a clustered index  on 1 column and I am joining those 2 columns together. I was thinking this join should be instant, but its taking longer than I expected. The query looks like this: SELECT a.Journal_ID FROM Fact.TableName a  INNER JOIN Journal.TableName j ON j.ID = a.Journal_ID   The execution plan is doing a index scan on each table (not on the clustered index) and then doing a hash match on the results.   In reality I want a bunch of columns back from "a" and a handful from "b". What would be the ideal index design in this example?Craig

Converting DateTime to SmallDateTime when OUTER JOIN

  
Hi, Here is the situation: I have a query with the following format:       Select a.*, b.* from (select col1, col2 from tbl1 t1 left join tbl2 t2 on t1.x = t2.x where t1.colSmallDateTime= MAX(t.ColDateTime)) a                     LEFT OUTER JOIN (select col1, col2 from tbl1 t1 left join tbl2 t2 on t1.x = t2.x where t1.colSmallDateTime= MIN(t.ColDateTime)) b                     ON (a.y = b.y) When i tun this query i get the following error:            Msg 298, Level 16, State 1, Line 1            The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error. Does anyone have any idea why i am getting this error? Thank in advance for your help!  

SQL Server 2008 bug with outer join and index

  
Hello! We found a problem when migrating from SQL Server 2005 to SQL Server 2008. SQL-statements like   SELECT <table1>.<fields>, <table2>.<fields>, <table3>.<fields> FROM <table1> JOIN <table2> ON <table2>.<field> = <table1>.<field> LEFT JOIN <table3> ON <table3>.<field> = <table1>.<field> WHERE (<table2>.<field> = 2 AND <table2>.<another_field> = 'some_text' OR <table3>.<field> = 2 AND <table3>.<another_field> = 'some_text')   give different results on 2005 / 2008. On 2008 I get lots of exessive rows which have NULLs instead of the values required in the WHERE-term. Changing the Compability Level does nothing. It also doesn't matter from where the query is executed (direct via ODBC, in a view or from the Enterprise Manager). 2005/2008 also use different execution plans. (Btw: how can I make an screenshot of a EP larger than the screen?)   After some hours I drilled it down to the existence of a index! By deleting or deactivating the index 2008 gives the correct results like the 2005 did, after activating the index I get these exessive NULL-rows again!   Anyone else ran into this problem? Any tips (dropping all indexes in a 25+ GB DB is NOT an option :-) ?   Thanks in advance! Holger WellenkötterMCTS SQL 2005

Trouble with Outer Join

  
Hi, I'm having an issue with an outer join due to my data structure. I have a view and table and I need to join them so that I get all the records from the table and the associated results from the view. The view structure is: Total | cID | cName | lID | lName 50       1       2 GB      1      Stock Room 100     2       4 GB      1      Stock Room The table structure is: cID | cName 1        2 GB 2        4 GB 3        8 GB 4       16 GB The result i'm looking for should therefore look like this returning all rows from the table, matching rows from the view and NULL for non-matching rows: cName | Total 2 GB       50 4 GB       100 8 GB       NULL 16 GB     NULL Probably something very basic i've missed. Thanks for the help in advance. MichaelWeb Developer MCTS SharePoint 2007 Developer

Problem with Merge Join and condition splt trasnformations in SSIS 2008

  
  Hey, While working SSIS in 2008 we have encountering some weird problems with Merge, Merge Join and Condition Spilt transformations, here are the details... Condition Split and Merge Join and Condition Split: Please check the below diagram(not really just tried) for the complete details. Just FYI data is in sorted order   condition Split -> 420000      | 18                                                          | 419982  Merge Join(left)  -- src1(6000)      |10  Condition Split      |9     |1   the merge join transformation is not returning the complete 18 rows to the next transformation that's why we are missing 8 rows. But if we adds a sort transformation (getting warning as data is already sorted) before merge then complete 18 rows were passing to the condition split. Also tried modifing the source query just to return 18 rows then also it was passing the 18 rows to next trasnformation except with full load.       Merge: We have two sorted data sources and first one has 40000 records and second source have 12000 records and after the Merge transformation we have Condition Split transfor

LINQ left outer join question

  
I can do this in other ways, but am looking for the "LINQ" way of doing this: Table1: People (PersonId,SchoolId)Table2: AssignedPeople (PersonId,SchoolId,RoomId) I want to select the people in Table1 within a particular SchoolId that are not in the assignedPeople table (in a particular Room).  The SQL is: select p.* from people p left outer join assignedpeople ap on p.PersonId=ap.Person and p.SchoolId=ap.SchoolIdand ap.RoomId=@roomId where p.SchoolId=@SchoolId and ap.PersonId is null I tried this LINQ (VB,NET):dim SchoolID as guid=...dim RoomID as guid=... (from p in dc.people where p.SchoolId=SchoolIdgroup join ap in dc.AssignedPeopleon p.PersonId equals ap.PersonId and p.SchoolId equals ap.SchoolId into groupfrom g in group.DefaultIfEmptywhere g.RoomID = nothing) but I can't get the ap.RoomId=@roomId part into the right place, should be something like:group join (from ap in dc.AssignedPeople where ap.RoomId=RoomId) Any help would be appreciated,Reuven

Outer join operators *= and =* are not supported in 90 or later compatibility modes Upgrade issue

  
Hi all In processes of upgrading SQL Server 2000 to SQL Server 2008 I held up with one issue I am not a good pragrammer If some helps me how to modify this query,realy it help for me. "Outer join operators *= and =* are not supported in 90 or later compatibility modes" Here this query SELECT Brnd.Name, Dev.Devype FROM Prd,PrdRel, Rel, RelVer, DevSoft, DevCla, Dev, Brnd,OVer WHERE Prd.ID = PrdRel. Product AND Rel.ID = PrdRel.Release AND RelVer.Release = Rel.ID AND DevSofte.ReleaseVersion = RelVer.ID AND DevSoft.DeviceClass = DevCla.ID AND DevCla.ID = Dev.DeviceClass AND Brnd.ID = Dev.Brand AND OVer.ID =* Dev.OSVersion AND RelVer.Latest = 'Y' AND Prd.ID = @Product AND Rel.OS IN (@OS) Please some help me how to code  to come out from the problem. your help going to be greate for me.  SNIVAS

Wierd case of: Msg 213, Column name or number of supplied values does not match table definition

  
Hi, I'm working on several triggers (that happen after insert or update) in order to log the changes in a different table. They all follow a similar syntax and are working fine, except for this one... I've reduced the next code to the minimum that gives an error, so we can safely assume the other parts of the trigger are working fine. INSERT INTO [Adt].[WardUnitStayLog] SELECT t.* FROM [Adt].[WardUnitStay] t INNER JOIN inserted i ON i.[Id] = t.[Id]; I've used this same syntax (but on different tables) in other triggers, and these are working perfectly fine. The above query provides the next error: Column name or number of supplied values does not match table definition. I've checked both tables for differences in the columns, but to no avail... (I've checked them manually and by outerjoining the information_schema.columns) (I've also checked the order in wich these columns are defined, they match over the two tables) These are the creation scripts for the tables: CREATE TABLE [Adt].[WardUnitStay] ( [Id] [dbo].[Id] IDENTITY(1,1) NOT NULL, [UnifiedUnitStayId] [dbo].[Id] NOT NULL, [WardCd] [dbo].[Cd] NOT NULL, [_FirstAtTm] [dbo].[Dtm] NOT NULL, [_IsReservation] BIT NOT NULL, [_LastAtTm] [dbo].[Dtm] NOT NULL, [_LastBedCd] [dbo].[Cd] NULL, [_LastPhysicianUid]

Left Outer Join in LINQ + Entities

  
HiI have found quite a bit of info on the net, but I can't get it to work. Here's the normal SQL query:SELECT cl.ID AS ClientID, cl.Name, cl.Surname, CLC1.PHONECODE + ' ' + CLC1.PHONENUMBER AS WorkNum, CLC2.PHONECODE + ' ' + CLC2.PHONENUMBER AS HomeNum, CLC3.PHONECODE + ' ' + CLC3.PHONENUMBER AS CellNum, CLC1.PREFERRED AS WorkNumPref, CLC2.PREFERRED AS HomeNumPref, CLC3.PREFERRED AS CellNumPref, veh.ID AS VehicleID, veh.MAKE, veh.MODEL, veh.YEAR, veh.REGNO, ws.ID AS WorkshopID, ws.JOBTYPE, ws.SERVICEADVISOR, ws.TIMEFINISHED, ws.TECHNICIAN, fs.ID as FollowupID, fs.TYPE, fs.DESCRIPTION, fs.SCHEDULEDATE, fs.WRONGNUMBERFLAG FROM CLIENTS AS cl LEFT OUTER JOIN CLIENTCONTACTNUMBERS AS CLC1 ON cl.ID = CLC1.CLIENTID AND CLC1.TYPE = 'W' LEFT OUTER JOIN CLIENTCONTACTNUMBERS AS CLC2 ON cl.ID = CLC2.CLIENTID AND CLC2.TYPE = 'H' LEFT OUTER JOIN CLIENTCONTACTNUMBERS AS CLC3 ON cl.ID = CLC3.CLIENTID AND CLC3.TYPE = 'C' INNER JOIN CLIENTVEHICLES AS clveh ON cl.ID = clveh.CLIENTID INNER JOIN VEHICLES AS veh ON clveh.VEHICLEID = veh.ID INNER JOIN WSBOOKINGS AS ws ON clveh.ID = ws.CLIENTVEHICLEID INNER JOIN FOLLOWUPSCHEDULE AS fs ON fs.WSBOOKINGID = ws.ID WHERE fs.ID = 2 There is a multiple join between CLIENTS and CLIENTCONTACTNUMBERS so it would return a single row of data. If you were to do a normal join on CLIENTS and C

left outer join linq

  
 Hi   I try to get this exsisting linq query to use a left outer join without success, can anyone show me how its supposed to be?   Dim product = (From f In db.tbl_Formats _ Join p In db.tbl_Products On f.FormatID Equals p.FormatID Where p.NodeId = iNodeID _ Select p, f.FormatName).SingleOrDefault   The FormatID is not always present and I therefore need a left outer join on the Formats table.   Best Regards

using join when a column may have multiple values

  
Have 2 tables. Table A has among several columns one called "product_code," which contains 4-digit numerals. Table B has just 2 columns, "product_code," the same 4-digit numerals used in the same column in Table A, and "product_description," which includes a VARCHAR string describing the product referenced by the code. I'm querying Table A and trying to include the "product_description" from Table B with each record returned. Am using a LEFT JOIN like this: SELECT  * FROM Table_A LEFT OUTER JOIN Table_B ON Table_A.product_code = Table_B.prod_code This works fine EXCEPT in cases where Table A has more than one value in "product_code," in which case I get no match in Table B and "NULL" is returned for "product_description." When there is more than one value in the "product_code" column in Table A for a particular record, the values are separated by commas (for example: 1002,1003,9856). How can I get this to work so that for records that have multiple produce codes in table A I get multiple product descriptions from Table B?   Thanks      

Get all data - LEFT OUTER JOIN with condition

  

Hi there

I have 2 tables related,

first = WBF_EVN_evn_event(keeps all event records),
second = WBF_EVN_ptc_participant(keeps all records related to participants)

I'm trying to get all events which the current user has not attended or not replied(if ptc_pts_id = 2 then the participant has not attended OR I want to see also where ptc_pts_id is NULL)

       SELECT                  evn_id, evn_name, evn_date_start, evn_date_end, ptc_pts_id, ptc_usr_id
       FROM                    WBF_EVN_evn_event
       LEFT OUTER JOIN         WBF_EVN_ptc_participant ON evn_id = ptc_evn_id
       WHERE                   evn_deleted = 0 
       AND                     (ptc_pts_id is NULL or (ptc_pts_id = 2 AND ptc_usr_id = 24 ) )       
       AND                     evn_dom_id = 1


With this query I get up to some point good result;if there is no other

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