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


Top 5 Contributors of the Month
david stephan
Asad Ali
Fauzul Azmi
Post New Web Links

Left Outer Join fails to return all records

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


View Complete Post


More Related Resource Links

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

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

how to return records in squence of inner join table?

  
Hi, I have test database with following script. I am trying to explain my problem with this sample db script. I am creating a temp. table with the ordered column from other table and then using that table to join the other table. If you notice the output of the below select query, the returned rows from first table are in the sequence of insertion not in the sequence of the temp. table. Is there any other way to retrieve rows in the sequence of temp. (joined) table? CREATE TABLE [dbo].[Table_2]( [c1] [int] NULL, [c2] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (1, N'z') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (2, N'y') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (3, N'x') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (4, N'a') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (5, N'b') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (6, N'c') CREATE TABLE [dbo].[Table_1]( [c1] [int] NULL, [c2] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (3, N'x') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (2, N'y') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (1, N'z') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (6, N'c') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (5, N'b') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (4, N'a&

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

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

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


Linq join fields for return data

  

Hello,
  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.Cat_GUID,
                                    c.Category_Key && " ;" && c.Category_Name // HOW CAN I DO THIS.....
                          

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/

More than one records return error

  
Hi Below is my query used in procedure. This query can return no records or more than one records. If this is the case then I wanted to return error message. How to do this? Select Band, SimpleBand into <complexband>, <simpleband> from HHSRS_HazardBand where lowerbound <= <hazardscore> and upperbound > <hazardscore>  

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

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

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

deletion of records after inner join

  
i have table Emp_service where i have data, but when i inner join the table with itself the records are not shown up: The code is: SELECT t.[Emp_KEY] ,t.[Emp_ID] ,t.[Emp_CODE] ,t.[dept_NUMBER] ,t.[Emp_DESIGNATION] ,t.[FACILITY_CODE] ,t.[DATE_OF_Hire] ,t.[DAYS_Of_Working] ,t.[ENTRY_NAME] ,t.[ENTRY_DATE] ,t.[UPDATE_NAME] ,t.[UPDATE_DATE] FROM [Emp_service] t INNER JOIN ( select MAX([Emp_KEY]) as [Emp_KEY] ,MAX( [Emp_ID]) as [Emp_ID] ,MAX([Emp_CODE] ) as[Emp_CODE] , MAX( [dept_NUMBER]) as[dept_NUMBER] ,MAX( [Emp_DESIGNATION]) as[Emp_DESIGNATION] ,MAX( [FACILITY_CODE]) as [FACILITY_CODE] ,MAX([DATE_OF_hire]) as [DATE_OF_hire] From [Emp_service] Group by [Emp_ID], [Emp_CODE], [dept_NUMBER], [Emp_DESIGNATION], [FACILITY_CODE], [DATE_OF_hire] ) a ON a.[Emp_KEY]= t.[Emp_KEY] I have record for that particular Emp_Key in table Emp_service, but aftre running this query i am not getting records Can anybody please tell me why it is showing this abnormal behaviour. Any help will be appreciated. Thanks, Punia

Problem with CTE return only last 5 dates , Join on dates ?

  
Declare @todate datetime, @fromdate datetime Select @fromdate=DateAdd(dd, DateDiff(dd, 0, GetDate()), 0) ,@todate=DateAdd(dd, DateDiff(dd, 0, @fromdate) -5, 0) -- select @fromdate as Today,@todate as N'5 days Back' ;With DateSequence( Date ) as ( Select @fromdate as Date union all Select dateadd(day, -1, Date) from DateSequence where Date >= @todate ) select * from DateSequence I'm missing something i just need the CTE to return last 5 dates from current date . Does join on dates , really join on datetime ? select ds.Date,et.* from DateSequence ds left outer join emp_timings et on ds.Date=et.dt and et.id=100 Above query efficient to pick last five days records ? Thanks in advance .Rajkumar Yelugu
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