.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

left outer join

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

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




View Complete Post


More Related Resource Links

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;  

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

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


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/

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

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

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


NULL for user who don't have appointment - UNION error ... LEFT JOIN doesn't work

  

Hi,

It's so silly what i'm trying to do.. I just need to catch the entire list of user and null for users who don't have appointment ....

I tried union but I've got an error, please help

 

Msg 205, Level 16, State 1, Line 2
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.




declare @SQL Varchar(4000)

declare @CRM_FilteredAppointment nvarchar(2000)
declare @CRM_FilteredSystemUser nvarchar(2000)



set @CRM_FilteredSystemUser = 'SELECT systemuserid, eu_reporthubname, fullname, businessunitidname 
 FROM FilteredSystemUser 
 WHERE businessunitidname = ''Spain''
'
--exec (@CRM_FilteredSystemUser)

set @CRM_FilteredAppointment = 'SELECT activityid, ownerid, regardingobjectid, subject, scheduledend 
 FROM FilteredAppointment 
'

set @SQL = ' 
 SELECT SU.systemuserid, SU.fullname, count(partyid)
 FROM (' + @CRM_FilteredSystemUser + ') AS SU
   LEFT JOIN
    (' + @CRM_FilteredAppointment + ') as Apt ON 
    SU.systemuserid = Apt.ownerid 
   INNER JOIN
    FilteredActivityParty AS AP ON Apt.activityid = AP.activityid 
   AND (AP.participationtypemask = 8 OR AP.participationtypemask = 5) 
 WHERE Partyidname is not null 
group by SU.systemuserid, SU.fullname
UNION ALL 
SELECT

Help with LINQ left join

  

Hi,

Im having a little trouble getting the syntax right in the following LINQ query.

            var merchants =
from m in dtM.AsEnumerable()
join mp in dtMP.AsEnumerable()
on m.Field<int>("MerchantId") equals mp.Field<int>("MerchantId")
into tmpProfile
from t in tmpProfile.DefaultIfEmpty()
where m.Field<string>("Name").ToLower().Contains(searchText.ToLower()) ||
t.Field<string>("Profile").ToLower().Contains(searchText.ToLower())
select m.Field<int>("MerchantId");

The problem is the highlighted line where I'm attempting to make a decision on a field that is potentially null/empty.  Anyone know how I get round this?


Thanks


Ms sql server left join problem

  

Hi, i have 2 tables(prduction and transact) that contain data for transactional updates per product, stock is calculated by doing sum() on one column in each table that contains the weight and to get the result per product i use group by product. My problem is that if than one value exists in the transact table, the sum(production.plannedtonnage) result is doubled for every record in the transact table. How can i fix this, any help would be greatly appreciated!

select distinct production.product

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