In Outter Joins, What Makes A Table Either Right Or Left?
Dec 16, 2002
Until today, I was always under the impression that left vs. right was determined by which side of the comparison operator the table was located.
In other words:
LEFT JOIN LeftTable.ID = RightTable.ID
would pull all the records from LeftTable and those that matched from from RightTable and that:
RIGHT JOIN RightTable.ID = LeftTable.ID
would pull exactly the same result set but I was wrong. So, if it is not the table position in relation to the comparison operator, is it simply that the tables listed first in the FROM clause aren the ones "Left" of those subsequently entered?
Ok I have an SQL statement that is driving me crazy. I have an item table (tblitem), a sales table (tblSales) and a branch transfer table (tblBranchTransfer) what I'm trying to do is return a list of all the items with the quantity the total quantity sold and the total quantity requested from other branches for each item. My code is as follows: select i.itemid, Sum(s.Sales) As "Quantity Sold", Sum(t.Quantity)As "Quantity Requested" from tblitem iLeft Join tblSales s On i.itemid = s.ItemIdLeft Join tblBranchTransfer t On i.itemid = t.ItemId and TransferFlag = 'F' group by i.itemid The Quantity Sold appears fine however the Quantity Requested returns the wrong values. If for example item 1 appears twice in the tblSales and the total Quantity Requested is 10 then it will multiply that by 2 (the number of rows in tblSales). Does anyone know how to stop this from happening?
Hello All & Thanks in advance for your help!Background:1) tblT_Documents is the primary parent transaction table that has 10fields and about 250,000 rows2) There are 9 child tables with each having 3 fields each, their ownPK; the FK back to the parent table; and the unique data for thattable. There is a one to many relation between the parent and each ofthe 9 child rows. Each child table has between 100,000 and 300,000rows.3) There are indexes on every field of the child tables (though Idon't believe that they are helping in this situation)4) The client needs to be presented a view that has 5 of the mainfields from the parent table, along with any and all correspondingdata from the child tables.5) The client will select this view by doing some pattern-matchingsearch on one of the child records' detail (e.g. field-name LIKE%search-item% - so much for the indexes...)Problem:When I do the simple join of just the parent with one of the children,the search works *fairly* well and returns the five parent fields andthe corresponding matching child field.However, as soon as I add any one of the other child records to simplydisplay it's unique data along with the previously obtained results,the resulting query hangs.Is the overall structure of the tables not conducive to this kind ofquery? Is this a situation where de-normalization will be required toobtain the desired results? Or, more hopefully, am I just an idiotand there is some simpler solution to this problem?!Thanks again for your assistance!- Ed
Im having a problem with a statement i cannot seem to get 2 left joins working at the same time 1 works fine but when i try the second join i get this error:-
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx'.
My SQL statment is as follows :- SELECT children_tutorial.*,schools.schoolname,regions.rname FROM children_tutorial LEFT JOIN schools ON children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx
I am using an Access database i have tried all sorts to get it working and its driving me mad!! any help would be really appreciated.
I'm trying to write a 3 table query using two LEFT JOINs. Originally, I only had one LEFT JOIN and prior to the addition of the the third table (parts) this query worked. Now it doesn't. I think it has to do with my GROUP BY.
SELECT quote.quote_id, parts.material, machining_operations.machine, machining_operations.per_roughing, machining_operations.per_of_machining, machining_operations.programming_time, machining_operations.setup_time, machining_operations.cycle_time, machining_operations.notes quote.part_name, quote.revision_no, quote.quantity, quote.initial_volume, quote.final_volume, quote.material_price, machining_operations.mo_id FROM quote LEFT JOIN machining_operations ON machining_operations.quote_num = quote.quote_id LEFT JOIN parts ON parts.package_no = quote.package_no AND parts.part_name = quote.part_name GROUP BY quote.quote_id
I was writing a query using both left outer join and inner join. And the query was ....
SELECT S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname FROM Production.Suppliers AS S LEFT OUTER JOIN (Production.Products AS P INNER JOIN Production.Categories AS C
[code]....
However ,the result that i got was correct.But when i did the same query using the left outer join in both the cases
i.e..
SELECT S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname FROM Production.Suppliers AS S LEFT OUTER JOIN (Production.Products AS P LEFT OUTER JOIN Production.Categories AS C ON C.categoryid = P.categoryid) ON S.supplierid = P.supplierid WHERE S.country = N'Japan';
The result i got was same,i.e
supplier country productid productname unitprice categorynameSupplier QOVFD Japan 9 Product AOZBW 97.00 Meat/PoultrySupplier QOVFD Japan 10 Product YHXGE 31.00 SeafoodSupplier QOVFD Japan 74 Product BKAZJ 10.00 ProduceSupplier QWUSF Japan 13 Product POXFU 6.00 SeafoodSupplier QWUSF Japan 14 Product PWCJB 23.25 ProduceSupplier QWUSF Japan 15 Product KSZOI 15.50 CondimentsSupplier XYZ Japan NULL NULL NULL NULLSupplier XYZ Japan NULL NULL NULL NULL
and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.
This may be a very simple problem but it's been racking may brains for a while and I just can't seem to think it through clearly.
I'm trying to return a query which uses a left join and where. I'm hoping to get a result set which shows - let us says all the departments in a company. I would like to see all the department but only the names of department heads that earn 20.000+.
In MS Access I used a subquery. My subquery returned only departments with department heads that earned 20.000+ - I then left joined the departments table to that query - no problem.
With MSSQL I've tried IN, ANY, ALL but my result sets only returns the departments that earn 20.000+ and the employees for those particular departments.
I'm thinking there must be some way of doing this without having to use a union clause.
Thanks for taking the time to read this message through.
My understanding of relevant topics as well as SQL Books Online definition of left outer joins is that each record in the left table will be retrieved and where no associated right record exists then null values will be displayed for records in the right hand table but I've obviously misunderstood and would be grateful if someone could show me how to produce the required effect.
My scenario is pretty simple: 2 tables DiningTables and Reservations with columns as follows:
DiningTables: TBL_ID and TBL_Location - TBL_ID is the primary key
Reservationss::RES_TBL_ID and RES_Diner_Name - RES_TBL_ID is the primary key
There are 8 records in DiningTables and 4 records in Reservations and the objective is obtain the following output:
TBL_ID RES_Diner_Name
1 Jones
2 Smith
3 Bloggs
4 Mack
5 null
6 null
7 null
8 null
The SQL query I used is
SELECT Reservations.RES_Diner_Surname, DiningTables.TBL_ID FROM DiningTables LEFT OUTER JOIN Reservations ON DiningTables.TBL_ID = Reservations.RES_TBL_ID
That query generates 11 rows as follows:
TBL_ID RES_Diner_Name
1 Jones
1 Smith
1 Bloggs
1 Mack
2 null
3 null
4 null
5 null
6 null
7 null
8 null
I'm clealry missing something incredibly obvious and I kinda feel like the village idiot and would be extremely grateful for a clue!!
This may be a stupid question. But I just want to make sure i'm going in right direction. I wrote the following query. My purpose is to retrieve all the members who submitted loan applications. I just want to make sure my query is right? Can anyone veryfy this query? Do I have to use loanApplication table first instead of members. Also Do I need to use right outer joins instead of left outer joins?
Code Block SELECT Member.CUMemberId, LoanApplication.SubmittedOn, Member.LastName, Member.FirstName, Member.MiddleName, LoanApplication.Amount, LoanApplication.Decision, LoanApplication.Term, Rate.InterestRate, LoanApplication.Status, Member.CuStatus FROM Member INNER JOIN MemberLogon ON Member.Id = MemberLogon.MemberFK INNER JOIN LoanApplication ON Member.LastLoanApplicationFK = LoanApplication.Id AND Member.Id = LoanApplication.MemberFK LEFT OUTER JOIN Account ON Member.Id = Account.MemberFK AND LoanApplication.LoanFK = Account.Id LEFT OUTER JOIN Rate ON LoanApplication.RateFK = Rate.Id WHERE (LoanApplication.Status = 'Submitted')
Hello, I am working on a query that has 11 left join statements, some are hitting against reference data that has a small amount of records, whereas others not so small. From a performance standpoint, should I look at rewriting this query, and how would I do so? What is an alternative to left joins; any examples anyone has? Thanks.
First of all, is this an appropriate place to get answers related to SQL CE? If not, do you have any recomended forums elsewhere?
I'm trying to get a list of various related tables using ResultSets on SQLCE 3. The query is something like this:
SELECT A.* , F.Descricao AS FamiliasDescricao , M.Descricao AS MarcasDescricao , I.Descricao AS IVADescricao FROM Artigos AS A LEFT OUTER JOIN Familias AS F ON A.FamiliasUID = F.UID LEFT OUTER JOIN Marcas AS M ON A.MarcasUID = M.UID LEFT OUTER JOIN IVA AS I ON A.IVAUID = I.UID INNER JOIN ArtigosTipos AS AT ON A.ArtigosTiposUID = AT.UID;
The column ArtigosTiposUID cannot be NULL, so an INNER JOIN is used, but the other UID columns can have a NULL value, and I need all the rows on Artigos to show up even if these other UIDs are NULL. The query runs fine like this in VS2005, returning NULL values for the columns if there are no rows on the other tables, both on the SQL Server 2005 database and the .sdf database used on the Windows Mobile device. But on Windows Mobile SQL CE gives me an "Unspecified error [7]", Native error 25607, an the stack trace ends with:
em System.Data.SqlServerCe.SqlCeCommand.ProcessResult s() em System.Data.SqlServerCe.SqlCeCommand.CompileQueryP lan() em System.Data.SqlServerCe.SqlCeCommand.ExecuteComman d() em System.Data.SqlServerCe.SqlCeCommand.ExecuteResult Set()
If I replace all the LEFT OUTER JOINs with INNER JOINs only the rows where all UIDs have a value show up, but as I said, I want all rows on tabela Artigos. Even if I remove all JOINs except the last one and replace it with a LEFT OUTER JOIN I get the same erro, all rows having the column ArtigosTiposUID defined... it seems as if the simple presence of LEFT OUTER JOIN makes SQL CE return an error.
Is there a way to run the queries on VS using the SQL CE engine so that one can check whether the query will run successfuly on Windows Mobile?
Could somebody tell me what is the secret of being able to write a SELECT statement having mulitple LEFT or RIGHT joins, I seem to get in trouble as soon as I add the second LEFT join, as I am obviously doing it wrong.
These are my tables, would somebody mind having a go,or explaining what do i need to be aware of in a case like this
Other than being much less readable, is there a downside to combining left and right outer joins in the same SELECT? I'm reviewing some generally poor code done by a contractor and it's peppered with queries with both left and right joins. I've always thought it was just a semantic difference, but I was just wondering if, other than readability, there were any performance issues.
I am working a DTS package and I need to Join to completely differnet tables in such a way that I need to do an inline view and an Outer Join. In this current form, it drops all columns for a day if one of the inline views returns null.
(SELECT COUNT(SDD_Status) AS On_Time , SDD_Date as On_Time_Date FROM SDD_Store_Delivery_Data_Table WHERE SDD_Route LIKE '01%' AND SDD_Status = 'On Time' AND SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004' GROUP BY SDD_Date) a,
(SELECT COUNT(SDD_Status) AS Early ,SDD_DATE As Early_Date FROM SDD_Store_Delivery_Data_Table WHERE SDD_Route LIKE '01%' AND SDD_Status = 'Early' And SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004' GROUP BY SDD_Date) b,
(SELECT COUNT(SDD_Status) AS Late , SDD_Date As Late_Date FROM SDD_Store_Delivery_Data_Table WHERE SDD_Route LIKE '01%' AND SDD_Status = 'Late' AND SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004' GROUP BY SDD_Date) c,
(SELECT SUM(CAST(SDD_Stay AS NUMERIC)) AS AVG_Duration , SDD_Date As Stay_Date FROM SDD_Store_Delivery_Data_Table WHERE SDD_Route LIKE '01%' AND SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004' GROUP BY SDD_Date) d,
(SELECT DISTINCT(SDD_Date) AS DelDate FROM SDD_Store_Delivery_Data_Table WHERE SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004' GROUP BY SDD_Date)e,
I am trying to understand the concept of left joins. I have the following query and am not sure about the left joins.
I am familiar with joins but the left join below is a little confusing.Below it seems like a third table is involved. Is this because there is no column to map to in the from table? Also, since tables sl and sc are mapped based on the SecurityID column and sl and ex do not have any common columns, table sc is mapped to ex using the left join? Which table's data will be returned based on the left join?
I checked the column type for the Exchange column(ex.LSECode) and it appears varchar(3).
Hi,I'm curious about the computational complexity of a query I have. Thequery contains multiple nested self left joins, starting with a simpleselect, then doing a self left join with the results, then doing a selfleft join with those results, etc. What puzzles me is that the timerequired for the query seems to grow exponentially as I add additionalleft joins, which I didn't expect. I expected the inner select toreturn about 25 rows (it does), then I expected the self join to resultin about 25 rows (it does), etc. Each join just adds another column; itdoesn't add more rows. So the left part of the join is staying the samesize, and so is the right part of the join, since I'm always joiningwith the same table.So I would think the time for this query should be (time to join 25rows against the source table) * (num joins), but it seems to besomething like (num rows) ^ (num joins). Any ideas? I'm just trying tounderstand the system a little better. (But if you have any ideas aboutimproving the query, I'm always open to those, too.)The execution plan is what you'd expect: an index seek loop-joined withanother index seek, the results of which are merge-joined with anotherindex seek, the results of which are merge-joined with another indexseek, ad nauseum, until a final "compute scalar cost (39%)" and "select(0%)"For the brave and curious, I've pasted the query below.Thanksselect right(x.cp_yyyymm, 2)+'-'+left(x.cp_yyyymm, 4) as [Month],table0.cp_num_loans/1 as [AFCM9704], table1.cp_num_loans/1 as[AFC9104], table2.cp_num_loans/1 as [BFAT01C], table3.cp_num_loans/1 as[BFAT02B], table4.cp_num_loans/1 as [BFAT03D], table5.cp_num_loans/1 as[BFAT03E], table6.cp_num_loans/1 as [BFAT03F], table7.cp_num_loans/1 as[BFAT04A], table8.cp_num_loans/1 as [BFAT04C], table9.cp_num_loans/1 as[BFAT04D], table10.cp_num_loans/1 as [BFAT99C] from (((((((((((selectdistinct cp_yyyymm from cp_deal_history where cp_deal_id in('AFCM9704', 'AFC9104', 'BFAT01C', 'BFAT02B', 'BFAT03D', 'BFAT03E','BFAT03F', 'BFAT04A', 'BFAT04C', 'BFAT04D', 'BFAT99C') and cp_yyyymmbetween 200304 and 200504) as x left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='AFCM9704') astable0 on x.cp_yyyymm=table0.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='AFC9104') as table1on x.cp_yyyymm=table1.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT01C') as table2on x.cp_yyyymm=table2.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT02B') as table3on x.cp_yyyymm=table3.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT03D') as table4on x.cp_yyyymm=table4.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT03E') as table5on x.cp_yyyymm=table5.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT03F') as table6on x.cp_yyyymm=table6.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT04A') as table7on x.cp_yyyymm=table7.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT04C') as table8on x.cp_yyyymm=table8.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT04D') as table9on x.cp_yyyymm=table9.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT99C') astable10 on x.cp_yyyymm=table10.cp_yyyymm order by x.cp_yyyymm
I have a query with 11 left joins. Some hits against tables with small amounts of reference data, whereas others are not so small. Should I rewrite this in another way, as performance is a requirement on this one? Or, should I do it another way?
I need to do multiple left outer join to return search profiles that could contain NULL in them that could also be foreign keys. I bolded the three IDs that could be NULL or have a foreign key for a value. An example with my code would be great I've tried decyphering the many employee and company examples on the web but I haven't figured it out yet. Right now I only get profiles that have foreign key values and it misses the rest in the search. So NULL MakeID or ModelID no result on that item my SQL statement below. Using SQL Server 2005. Pretty new this to SQL and databases but so far this has been the only trying part.
Thanks
String dbsql = "SELECT a.EquipmentID " + " , a.SerialNo " + " , b.Category " + " , c.Subcategory " + " , d.Make " + " , e.Model " + " , f.Status " + " FROM tblEquipInfo a " + " , tblEquipCat b " + " , tblEquipSubcat c " + " , tblEquipMake d " + " , tblEquipModel e " + " , tblStatus f " + " WHERE b.Category = '" + val + "' " + " AND a.CategoryID = b.CategoryID " + " AND a.SubcategoryID = c.SubcategoryID " + " AND a.MakeID = d.MakeID " + " AND a.ModelID = e.ModelID " + " AND a.StatusID = f.StatusID";
Hi, I am trying to write a query that gets the percentage of students in specific racial groups in specific schools. Some ethnicity values of students are null so I have to use left joins. My query is below, when I run it I get the error "join expression not supported", I've tried a couple different ways of doing it but I always get that error or "syntax error in from clause". Can anybody help me with formatting multiple and nested left joins in general?
Thanks in advance.
drop table percentMinorities; create view percentMinorities as select s1.schoolid, round(count(s2.studentid)/count(s1.studentid),2) as percentWhite, round(count(s3.studentid)/count(s1.studentid),2) as percentBlack, round(count(s4.studentid)/count(s1.studentid),2) as percentHispanic, round(count(s5.studentid)/count(s1.studentid),2) as percentAsian
from
students as s1 left join (students as s2 left join (students as s3 left join (students as s4 left join students as s5 on s4.studentid is not null and s5.ethnicity = 'A') on s3.studentid is not null and s4.ethnicity = 'H') on s2.studentid is not null and s3.ethnicity = 'N') on s1.studentid is not null and s1.ethnicity = 'O'
The following query should return a list of clr_id's that have a match in at least 1 of the other fields mentioned in the joins.
declare @keyWord varchar(40) set @keyWord = 'merc' set NOCOUNT on
SELECT distinct clr.clr_id FROM CLR LEFT OUTER JOIN CO ON CLR.CO_ID = CO.CO_ID LEFT OUTER JOIN CLR_NM ON CLR.CLR_ID = CLR_NM.CLR_ID LEFT OUTER JOIN CLR_USE_YR ON CLR.CLR_ID = CLR_USE_YR.CLR_ID LEFT OUTER JOIN MODL ON CLR_USE_YR.MODL_ID = MODL.MODL_ID LEFT OUTER JOIN PAINT_CD ON CLR.CLR_ID = PAINT_CD.CLR_ID WHERE co.long_nm like '%'+@keyWord+'%' OR clr_nm.clr_nm like '%'+@keyWord+'%' OR clr_use_yr.yr_num like '%'+@keyWord+'%' OR paint_cd.paint_cd like '%'+@keyWord+'%' OR modl.modl_nm like '%'+@keyWord+'%'
The query runs at 3secs. Could I improve the query somehow? I was thinking that, since I actually need a distinct set of clr_id's, I should somehow check only the clr_id's that don't have a match in any of the previous joins.
I have a situation where I'm trying to add a text field to allowlarge Notes to be linked to record in an existing table (rather thansimply adding the new field into the table since relatively fewrecords will have the Note)Tables are basically:T1IDInvNumFK----------------------40|142|243|244|1T2MatDetIdEqpNote-------------------------------------------------40|text44|additional NoteI'm trying to create a subform that will allow the user to view/updaterecords in T1 (there are other fields in T1 that aren't relevant tothe problem) and also add a record into the T2.EqpNote field or edit arecord that already exists.This query works in Access (allows me to type in text into the EqpNotefield and automatically create a new record in T2 with the T1.Id valuein the T2.MatDetId field) :SELECT T1.ID, T1.InvNumFK, T2.EqpNote, T2.MatDetIdFROM T1 LEFT JOIN T2 ON T1.ID = T2.MatDetIdWHERE (((T1.ID)=10230));but when I try to 'translate' it into a version that I can use for thesubform in the adp (it's an Access 2000 project with a SQL Server 2000backend) the 'child' record does not get added into T2.Here's the record source I'm using for the form in the adpSELECT T1.ID, T1. InvNumFK, T2.MatDetId, T2.EqpNote FROM T1 LEFTOUTER JOIN dbo.T2ON T1.ID =T2.MatDetIdWHERE T1.InvNumFk = XX--'master' tableCREATE TABLE [dbo].[T1] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[InvNumFK] [int] NOT NULL) ON [PRIMARY]GOALTER TABLE [dbo].[tblBSMaterialDet] WITH NOCHECK ADDCONSTRAINT [PK_T1] PRIMARY KEY NONCLUSTERED([ID]) ON [PRIMARY]GO--Sub tableif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[T2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[T2]GOCREATE TABLE [dbo].[T2] ([MatDetId] [int] NOT NULL ,[EqpNote] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOALTER TABLE [dbo].[T2] WITH NOCHECK ADDCONSTRAINT [PK_T2] PRIMARY KEY CLUSTERED([MatDetId]) ON [PRIMARY]
In SQL Server is there a quick way to automatically insert empty value instead of NULL? I have a long Insert-SQL command with LEFT JOIN and don't want to use ISNULL for every field.
Researching on this in the meantime myself and I believe there is no way.
I am having an issue with large queries using Microsoft SQL Server 2005 - 9.00.2221.00 (X64).
I have a query with many INNER/LEFT OUTER/RIGHT OUTER joins which is taking very very very long to run. This looks exactly like this problem described in http://support.microsoft.com/kb/318530. However, this doc says it was fixed in SP1, which is already installed.
Basically I have a query:
SELECT .... FROM TABLEA
INNER JOIN TABLEB ... LEFT OUTER TABLEC... LEFT OUTER TABLED... RIGHT OUTER TABLEF... LEFT OUTER TABLEJ.. LEFT OUTER TABLEH... LEFT OUTER TABLEI... RIGHT OUTER TABLEK... LEFT OUTER TABLEM.. ... 17 joined tables in all...... WHERE TABLEB.field1 = 'abc'
The query plan for this is using TABLEA as the "main" table and joining everything else to it. The problem is, TABLEA has 117 MILLION records. TABLEB has 10,000 records which match the WHERE. I stopped this query after it ran for 62 HOURS.
If I simply change the query to:
SELECT .... FROM TABLEB
INNER JOIN TABLEA ... LEFT OUTER TABLEC... LEFT OUTER TABLED... RIGHT OUTER TABLEF... LEFT OUTER TABLEJ.. LEFT OUTER TABLEH... LEFT OUTER TABLEI... RIGHT OUTER TABLEK... LEFT OUTER TABLEM.. ... 17 joined tables in all...... WHERE TABLEB.field1 = 'abc'
The query runs in 15 mins. The query plan now uses TABLEB and the WHERE clause to join all the other tables.
The problem is, this query is generated from a report writter, and I have no control over the way it creates the SQL code.
We have a view with many left joins. The original creators of this view might have been lazy or sloppy, I don't know. I have rewritten the query to proper inner joins where required and also nested left joins.
So rather then the following exemplary fragment
select <many items> from A left join B on B.id_A = A.id left join C on C.id_B = B.idthis now looks like select <many items> from A left join (B join C on C.id_B = B.id ) on B.id_A = A.id
Compilation time of the original view was 18s, of the new rewritten view 4s. The performance of execution is also better (not counting the compile of course). The results of the query are identical. There are about 30 left joins in the original view.
I can imagine that the optimizer has difficulty with all these left joins. But 14s is quite a big difference. I haven't looked into detail in the execution plans yet. I noticed that in both cases the Reason for Early Termination of Statement Optimization was Time Out.
I have a table which visibility can be toggled by a text box. By default it is invisible. After it is made visible, clicking a sortable column header makes the table invisible. Does this mean sorting makes the table go back to its default visibility?
Facing a strange problem, but obviously not expected earlier on, I am trying to execute a stored procedure via ADO which refrences a linked server table and I get an error specifying " OLEDB provider SQLOLEDB does not contain table "<DatabaseName>"."<owner>"."<tableName>" "
Has any one else encountered this problem before ? regards Govind
I am using stored procedure to load gridview but problem is that i am not getting all rows from first table[ Subject] on applying conditions on second table[ Faculty_Subject table] ,as you can see below if i apply condition :-
Faculty_Subject.Class_Id=@Class_Id
Then i don't get all subjects from subject table, how this can be achieved.
Sql Code:- GO ALTER Proc [dbo].[SP_Get_Subjects_Faculty_Details] @Class_Id int AS BEGIN
I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.
To join the table but MUST follow the condition as bitActiv = TRUE: select emp.nvcEmpName, emp.nvcEmpAddress, ety. nvcEmployeeType from cst_EmpProfile emp left join cst_EmpType on emp.intEmployeeTypee = ety.intEmpType and emp.bitActiv = 1.
But, the sql statement doesnt output the my expected result. Because the data row return must be 1st and 2nd row as it bitActiv = true. So, how's I going achieve what i want. tq.
Just curious if anyone has any in depth knowledge of how table join filtering works:
SELECT col1 FROM tbl1 a INNER JOIN tbl2 b ON a.col2 = b.col2 AND a.col3 = 1
(versus)
SELECT col1 FROM tbl1 a INNER JOIN tbl2 b ON a.col2 = b.col2 WHERE a.col3 = 1
Running some simple tests, the executions plans look identical. Does anyone know if/when either of these options would be preferential over the other? This is a rather difficult topic to Google and find any decent information.
I was looking at some sample queries on how to do product promotion. Basically im after the top few products that have been ordered along with a given product ID. I think this should do it, but wasnt sure about the 2nd join...and why ?
SELECT ProductID, ProductName, SUBSTRING(Description, 1, 150) + '...' AS Description FROM Products WHERE ProductID IN ( SELECT TOP 5 details2.ProductID FROM OrderDetails details1 INNER JOIN OrderDetails details2 ON details1.OrderID = details2.OrderID WHERE details1.ProductID = @ProductID AND details2.ProductID != @ProductID GROUP BY details2.ProductID )
