.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

Trouble with Outer Join

Posted By:      Posted Date: September 08, 2010    Points: 0   Category :Sql Server
 
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


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/

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;  

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

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

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

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

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

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

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

Dynamic LINQ Outer Join

  

Hey folks,

I want to do an outer join on an IQueryable without LINQ.

I have this query on all my services which differs only by one property reference. I want to abstract it away but I need to substitute the property reference with something I can pass in as an argument.

Heres the query:


var secureList = (from l in list
                join ep in meta.EntityEffectivePermissions on new { EntityId = l.AnnouncementId, EntityType = "Announcement" } equals new { EntityId = ep.EntityId.Value, EntityType = ep.Name } into tempEEP
                from eep in tempEEP.DefaultIfEmpty()
                where !SecurityState || (eep.UserId == UserId && ((int)eep.Permissions & (int)Shivam.ASL.Core.Security.Permission.List) == (int)Shivam.ASL.Core.Security.Permission.List)
                select new { EntityId = l.AnnouncementId, Permissions = eep.Permissions.GetValueOrDefault() }).Distinct();


So the l.AnnouncementId (and obviously the string that I set to EntityType but that's easy) differ. I figured I could write this query using the IQueryable Join method but the documentation on this is just awful. I have found an example on how to use it but I am unsure as how to achieve a left join effect with it. Can someone help?



Regards,
Sam


Left Outer Join

  


select A.names,B.address,C.age from A left outer join B on A.names=B.names left outer join C on C.age=B.age

select A.names,B.address,C.age from A left outer join B on A.names=B.names left outer join C on C.names=B.names

select A.names,B.address,C.age from A left outer join B on A.names=B.names left outer join C on C.address=B.address

select A.names,B.address,C.age from A left outer join B on A.names=B.names left outer join C on A.age=B.ages

how does this work, all the data in the tables A,B & C are the same, is the last query valid,bocz 'on' condition is not for the table of the left outer join


left outer join

  

Hi All,

I have one confusion. I have been told that if I am joining the two tables using the left outer join then I cannot use columns from the right side table  in where condition, otherwise it will act as a inner join.

e.g. select tr.* from trans tr LEFT OUTER JOIN detail d on tr.hprop = d.hprop where tr.hmy between 1 and 100 and d.hprop = 2 ....(will this act as inner join as I have mentioned d.hprop =2 in where condition?)

Is it correct? anf if yes then how?

 Please Help.

Thanks!

Sachin :)


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
                     

Trouble with UpdateProgress showing...

  

Recently I converted a project to VS.NET2010 and ASP.NET 4.0 (not convinced this is the problem, but regardless this is when the problem began) and now I am having an issue with an AJAX UpdateProgress showing. Here is the abrriviated code:

   <asp:UpdatePanel ID="UpdatePanelPostPageLoad" runat="server">
      <ContentTemplate>
         <asp:Timer ID="tmrPostPageLoad" Enabled="false" Interval="100" OnTick="PostPageLoad_LoadData"
            runat="server">
         </asp:Timer>
         <asp:Timer ID="tmrPostPageLoadPt2" Enabled="false" Interval="1" OnTick="PostPageLoad_LoadDataPt2"
            runat="server">
         </asp:Timer>      
                 
         <%--Placing the UpdateProgress inside the UpdatePanel targeted--%>
         <asp:UpdateProgress ID="UpdateProgressPostPageLoad" runat="server" DisplayAfter="0" AssociatedUpdatePanelID="UpdatePanelPostPageLoad">
             <ProgressTemplate>
                  <div>
                       Updating blah blah....
                  </div>
             </ProgressTemplate>
         </asp:UpdateProgress>

      </ContentTemplate>
   </asp:Up

Nested Tracers - is there a way to only log the outer trace?

  

Hi,

I have some nested tracers. The outer one has a category of 'PageTrace' and the inner ones have categories of 'DBTrace'. When I set the outer category source to 'All' I get both traces. When I switch on the inner trace, I just get the inner trace.

So, my question: is there any way to just log the outer trace events, i.e. the Page Traces.

Cheers,

James

 


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