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,
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.
We find that a delete command on a table where the rows to be deleted involve an inner join between the table and a view formed with an outer join sometimes works, sometimes gives error 625.
If the delete is recoded to use the join key word instead of the = sign then it alway gives error 4425.
625 21 0 Could not retrieve row from logical page %S_PGID by RID because the entry in the offset table (%d) for that RID (%d) is less than or equal to 0. 1033 4425 16 0 Cannot specify outer join operators in a query containing joined tables. View '%.*ls' contains outer join operators. The delete with a correleted sub query instead of a join works.
Error 4425 text would imply that joins with view formed by outer joins should be avoided.
The data I am pulling is correct I just cant figure out how to order by the last 8 numbers that is my NUMBER column. I tried adding FOR XML AUTO to my last line in my query: From AP_DETAIL_REG where AP_BATCH_ID = 1212 and NUMBER is not null order by NUMBER FOR XML AUTO) as Temp(DATA) where DATA is not null
but no change same error. Output: 1234567890000043321092513 00050020
Select DATA from( select '12345678'+ left( '0', 10-len(cast ( CONVERT(int,( INV_AMT *100)) as varchar))) + cast (CONVERT(int,(INV_AMT*100)) as varchar) + left('0',2-len(CAST (MONTH(DATE) as varchar(2))))+ CAST (MONTH(DATE) as varchar(2)) + left('0',2-len(CAST (day(CHECK_DATE) as varchar(2)))) + CAST (day(DATE) as varchar(2))+right(cast (year(DATE)
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 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";
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?
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'
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
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'm having problems with queries that use a view in an outer join. It's just sits there and grinds. Below is some queries to illustrate.
SELECT EvalID,RecIndex FROM dbo.vCurrentEvalInfo
Executed in 1 second in SQL Query Analyzer Returns 29,820 Rows ----
SELECT RecIndex FROM dbo.tblCNAKeyInformation
Executed in 1 second in SQL Query Analyzer Returns 47,978 Rows ----
SELECT C.RecIndex, E.EvalID FROM dbo.tblCNAKeyInformation C LEFT OUTER JOIN dbo.vCurrentEvalInfo E ON C.RecIndex=E.RecIndex
Executed in 1 second in SQL Query Analyzer Returns 47,978 Rows ----
SELECT C.RecIndex, E.EvalID, E.DATE_COMP, E.EvalDate, E.EvalCode, E.EvalResults FROM dbo.tblCNAKeyInformation C LEFT OUTER JOIN dbo.vCurrentEvalInfo E ON C.RecIndex=E.RecIndex
Executed in 1 second in SQL Query Analyzer Returns 47,978 Rows ----
Never Finishes Executing. If I include any column from the dbo.tblCNAKeyInformation and the query just grinds.
exampe: SELECT C.RecIndex, E.EvalID, E.DATE_COMP, E.EvalDate, E.EvalCode, E.EvalResults, C.LastName FROM dbo.tblCNAKeyInformation C LEFT OUTER JOIN dbo.vCurrentEvalInfo E ON C.RecIndex=E.RecIndex
Also, the below query doesn't finish executing either. SELECT RecIndex FROM dbo.tblCNAKeyInformation WHERE (RecIndex NOT IN (SELECT RecIndex FROM dbo.vCurrentEvalInfo))
Any I missing something Here? I seems like it's recalcualting the view with each scan of the outer table on the join instead of using a cached copy for x number of micro seconds ago.
I could solve this my dumping the view to a temp table, however that seems like an ugly hack to me.
I have a multiple table dataset that needs to be returned, with at least 5 joins, some inner, some left outer.
Currently, this is done via a parameterized stored prodedure, which is used fairly frequently. The parameters only affect the where clause, not the joins.
Would it be better to create the view with the joins already done, then pass in the parameters with the stored procedure? Which is better for overall performance? Which is better for quicker response times to the calling asp.net application?
The code below is from a nested view, which I've read should be avoided. I've also noticed GETDATE() is used, which I believe causes GETDATE() to be executed for every record selected (correct me if I'm wrong). I'm also guessing a JOIN containing a UNION against a SELECT statement is not a good idea. What other problems do you notice?
SELECT trans.Entry_Code, trans.D_C, trans.ADP_Security_# , trans.TRID, trans.Batch_Code, trans.Last_Money, null as Shares, Settle_date as Process_Date, null as Closing_Price, trans.Dwnld_Date, trans.Acnt, null as Mktval, cast(Null as varchar(20)) as Cusip_#, ACT.dbo.account.account_key AS account_key FROM (SELECT * FROM ADPDBBOOK.dbo.YTD05B WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15) UNION SELECT * FROM ADPDBBOOK.dbo.YTD06B) trans INNER JOIN ACT_DATA.dbo.account ON ACT_DATA.dbo.account.account_key = RIGHT(trans.Acnt, 5) INNER JOIN tbl_Accounts_TransactionalData ON trans.Acnt = tbl_Accounts_TransactionalData.Acnt
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.
I need to left pad the column with 0 if it is less than 4 characters long and extract the first 2 characters on the left into a new column COUNTY_CODE.
How can I do that in transact SQL?
I tried:
SELECT RIGHT(RTRIM('0000'+ISNULL([Code],'')),4) FROM [Place] WHERE [Place Code]='B' and [Code]='627'
And I got 0627. And how do I extract the first 2 characters?
How to remove space left to right and right to left
If I give limit >60 for first 60 character; limit 60< second 60 character
Result would be check if space at 60 character if yes remove and go the 59 character check then space remove and 58 character check if there is charater then display
As well as after 60 character to till 120 for right space
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
SELECT * FROM a LEFT OUTER JOIN b ON a.id = b.id instead of
SELECT * FROM a LEFT JOIN b ON a.id = b.id
generates a different execution plan?
My query is more complex, but when I change "LEFT OUTER JOIN" to "LEFT JOIN" I get a different execution plan, which is absolutely baffling me! Especially considering everything I know and was able to research essentially said the "OUTER" is implied in "LEFT JOIN".
SQL Server 2000Howdy All.Is it going to be faster to join several tables together and thenselect what I need from the set or is it more efficient to select onlythose columns I need in each of the tables and then join them together?The joins are all Integer primary keys and the tables are all about thesame.I need the fastest most efficient method to extract the data as thisquery is one of the most used in the system.Thanks,Craig
