I have a very silly question.Does anyone know What is result of Inner Join ( left Join, Outer Join)
I tried to search but did not find any results for this.I would really appreciate if someone helps me out.
Thanks,
Siri
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.
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
Hi, I need to change some SQL statements in Oracle to to SQL Server syntax. For example, the following is a query in Oracle SQL syntax. ================================================== =========================== SELECT COUNT(*) FROM TABLE1 T1, TABLE2 T2, TABLE3 T3, TABLE4 T4, TABLE5 T5 WHERE ( T1.NO(+)=T3.NO AND T3.NO=T2.NO(+) AND T4.NO(+)=T3.NO AND T3.NO=T5.NO(+) ) AND ( T1.CODE(+)='VALUE1' AND T2.CODE(+)='VALUE2' AND T4.CODE(+)='VALUE3' AND T5.CODE(+)='VALUE4' ) ================================================== ========================= Can you anyone please tell me the SQL Server equivalent? I am having doubts in the outerjoin part. Thanks in advance Regards R
Hi I have a requirement where in i haev to convert the SQL from Oracleto the one which will run on the SQL server.in the Oracle Query i am doing multiple joins, between some 13 tables.and some of these joins are inner joins and some are Left outer joins.table1 inner joined with table 2table2 inner join with table3table2 inner join with table4table2 left join with table5table5 left jin with table6table6 left jin with table7table7 left jin with table8table8 left jin with table9Any idea how to achieve this??Tianaren
Why is it that SQL joins (*=) run a little faster as opposed to ANSI joins(LEFT JOIN...)? Aren't they supposed to be almost identical?
The issue is this: we are promoting using ANSI syntax for the obvious reason (future versions of SQL Server may not support SQL Server syntax; portability, etc.)
However, the problem is the speed. What have others done about this? Do you use ANSI syntax or SQL syntax? HOw true is it that future SQL Server versions may discontinue support for the '*=" and "=*' join operators.
I have four tables which I want to return results for an advanced search function, the tables contain different data, but can be quite easily joined,
Table A Contains a Main Image, this image is displayed in the results Table B Contains an Icon, this image is displayed in the results Table C doesn't have an image in it but has a child table with a number of images associated to the table, in the UNION ALL statement I would Like to do a Join to get say the top Image from this child and print it for the row associated with table C.
Select title, description, image from tableA UNION ALL Select title, description, icon as image from tableB UNION ALL title, description, ( inner Join SELECT top(1) from imageTableC where imagetableC.FK = tableC.PK) as image from tableC
Could someone show me the syntax to do this, I have all the information printing to the screen, bar this table C image.
First, does anyone know any good SQL Server sites with articles and whatnot on query design, etc. Not so much basic "How to get data out of your table", but more complex topics like conditional stored procedures, working with triggers, etc. The MSDN is helpful but I often have trouble understanding what's going on.
And second, any pointers (or links) on how I can go about having a stored procedure query a pair of product tables to get information to display in my shopping cart? I've got:
So depending on if the 'IsCustom' field is True or False, I want to join the [cartitems].[partID] to either the [StdProducts].[stdpartID] or the [CustomProducts].[custompartID] to get the description and other information.
Eventually, I'll probably need to branch this type of procedure out even further (not just either/or scenario) to include the option of pointing to 6-12 different child tables depending on criteria within the parent table for a different scenario. Depending on performance, I could either hard code in the various child tables or have another table containing the table names and the appropriate key that would indicate which table to use.
Hopefully that made some sense, since I'm not entirely positive how to go about this type of thing or what you would call whatever I'm trying to do (and thus what I would be searching for).
CREATE TABLE A (ID INT IDENTITY (1,1)) CREATE TABLE B (ID INT, EMPID VARCHAR(10)) INSERT INTO A DEFAULT VALUES GO 5 INSERT INTO B VALUES (1,'E23') INSERT INTO B VALUES (1,'E24') INSERT INTO B VALUES (2,'E23')
from the above code i would like to get output like
I wanted to know if SQL Server 2000 does something behind the scenes in a transparent manner whenever records are inserted/deleted from two tables between which a join is defined based on a primary key to foreign key relationship. So I have already defined a parent-child relationship through the 'Database Diagram' between these 2 tables. I know when a table is indexed then SQL Server will perform some actions behind the scenes in a transparent manner.The reason I am asking this question is to know if its bad to define parent-child relatioship between 2 tables that will each contain thousands or millions of records.
My company wants me to research and flags or registry tricks that would allow non-ansi joins '=*' and '*=' in SQL Server 2005 with a compatiblity mode of 90 to be allowed.
The way I understand the situation is that in SQL Server 2005 with the database compatiblity set to 90, non-ansi join SQL such as the following would not work.
Select * from Customer, Sales Where Customer.CustomerID *= Sales.CustomerID
To work, the SQL above would have to be converted to ansi join SQL such as the following:
Select * from Customer LEFT OUTER JOIN Sales On Customer.CustomerID = Sales.CustomerID
Many hours would be spent browsing through millions of lines of code to find the non-ansi SQL and have changes made.
Does anyone know of any trace flaqs or registry entries that would allow SQL Server 2005 work in 90 compatiblity and still allow non-ansi =* and *= joins in SQL?
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.
Employee table - This table has EmployeeID, Name, DOB.
EmployeeDesignation table - 1 Employee can have many designations with each having an effective date. There is no flag to indicate which among multiples is the current entry. You can only figure it out by the newest/oldest EffectiveDate. I want to get the most recent and the oldest for each employee.
EmployeeSalaryHistory table - Structure/Design is similar to EmployeeDesignation table. I want to get the starting salary and current salary for each employee.
I want my query to output me the following fields...
Currently, I have a query to get this done which looks as below. Since I have more than 8K employees with each having multiple Designation and Salary entries, my query is taking forever.
selecte.EmployeeID, e.EmployeeName, e.EmployeeDOB, (select top 1 Designation from @EmployeeDesignation ed where ed.EmployeeID = e.EmployeeID Order By EffectiveDate) EmployeeStartingDesignation, (select top 1 Designation from @EmployeeDesignation ed where ed.EmployeeID = e.EmployeeID Order By EffectiveDate Desc) EmployeeCurrentDesignation,
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've successfully created a Linked Server that connects a local DB Engine with another DB Engine through an ip over an extranet. I am able to run simple Select statement queries on the Local DB Engine and get results from the linked server. However when attempting to perform more complex queries that join tables from the linked server with tables from the local DB server, I get the following error message after several minutes of execution:
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Protocol error in TDS stream". OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg -1, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
How I can resolve it. I've read on Distributed Transactions but I understand that it only applies to manipulation statements?
Both are SQL servers. Linked Server is SQL2008R2 if not mistaken. Local DB Engine is SQL2014.
I need to periodically import a (HUGE) table of data from an external data source (not SQL Server) into SQL Server, with the following scenarios: Some of the records in the external data source may not exist in SQL.Some of the records in the external data source may have a different value at different imports, but this records are identified univocally by the same primary key in the external datasource and in SQL Server.Some of the records in the external data source may be the same in SQL.
Due to the massive volume of the import, I would like to import only the records which are different from what I have in SQL Server (cases 1 and 2 above). In fact case 2 is the most critical.
I thought of making a query with a left outer join between the data in the external data source table (SOURCE) and the data in the SQL Server table (DESTIN). The join is done on the respective primary keys (composed keys of up to 10 columns) and one of the WHERE conditions will be that the value in SOURCE is different from the value in DESTIN.
The result of this query would be exactly what I need to import. How to do this in SSIS??? I couldn't figure out how to join tables in different data sources yet.
In fact I cannot write a stored procedure to do that, since one of the sources is in a datasources not SQL Server. I have seen the Lookup transformation in this article http://www.sqlis.com/default.aspx?311 but this is not exacltly what I want to do. Another possibility is to use the merge join, but due to the sorting I believe its performances would be terrible!
When I add a constraint to the where part of the SQL statement below it will only show the nc_department.department where siteid equals it. How do I pull and display all of those departments regardless of where the siteid = the selected item? In other words doesnt the Left outer Join supposed to grab all contents from the left table regardless?
SQL = "SELECT nc_department.order_id, nc_department.department, Count(nonconformance.department_id) as 'events', ISNULL(SUM(nonconformance.nc_wafer_qty),0) as wafers FROM nc_department LEFT OUTER JOIN nonconformance ON nc_department.department_id = nonconformance.department_id WHERE nc_department.active = '1' AND nonconformance.site_id = '" & siteid.SelectedItem.Value & "' GROUP BY nc_department.department , nc_department.order_id"
Soory if this is a bit basic, but I'm chasing my tail: I have two select statements: SELECT MyRows from Table1 where X = 1 SELECT OtherRows From Table 2 Where y = 3 I want to produce a LEFT JOIN between the result of each query, and return the resultant rows I know this is not correct syntax:
(SELECT MyRows from Table1 where X = 1) LEFT JOIN (SELECT OtherRows From Table 2 Where y = 3) ON Tabel1.Row1 = Table2.Row2
but I think it illustrates what I want to do If I do the WHERE after the join I only get where there is a righthand table I cant get the syntax right, an example with the correct grammar would be very much appreciated
Instead of inserting into, how can I use a join of multiple tables to update a table. This is the join that inserts.
INSERT INTO [table] (MyID, material, dollars) SELECT l.MyID, material, dollars FROM Tab_Client_Input l left outer JOIN Tab_Special_tox r ON l.MyID = r.MyID
Hi Folks, I writing a store procedure, the first three parts work pretty well. The last select statement has about 8 outer joins in it. every time I run the store procedure, I get an error message for the last part. Below are the error message and the store procedure:
Store Procedure:
--Create Procedure dbo.IMS_Donation
--AS
Select Distinct D_VST_ID as 'DRWLOC_ID', D_VST_INSTID as 'DRWLOC_INSTID' Into Donor_Visit1 From DNR_VST_DB_REC Where D_VST_DATE Between 20010101 AND 20040512 AND D_VST_DONTYP in ('AP', 'WB', 'RP', 'E2', 'E1') AND D_VST_STATUS = 'DN' ORDER BY D_VST_ID GO
SELECT DRWLOC_ID as 'COUNT_ID', DRWLOC_INSTID as 'COUNT_INSTID', count(*) as 'COUNT_VISITS' INTO Donor_Visit2 FROM DNR_VST_DB_REC, Donor_Visit1 Where D_VST_ID = DRWLOC_ID AND NOT EXISTS (Select R_DCC_ID From REC_DCC_DB_REC Where R_DCC_ID = DRWLOC_ID AND R_DCC_INSTID = DRWLOC_INSTID AND R_DCC_CALLCD = 'DC') GROUP BY DRWLOC_ID, DRWLOC_INSTID GO
SELECT DVT1.DRWLOC_ID as'COMP_ID', CMP.l_CMP_UNITNO as 'COMP_UNITID', CMP.L_CMP_INSTID as 'COMP_INSTID', count(*) as 'COMP_COMPTOT' INTO Donor_Visit3 FROM LAB_CMP_DB_REC CMP, Donor_Visit1 DVT1, DNR_VST_DB_REC VST, CMP_VST_Jct CVT WHERE CMP.L_CMP_INSTID = DVT1.DRWLOC_INSTID AND VST.D_VST_ID = DVT1.DRWLOC_ID AND VST.D_VST_UNITNO = CVT.L_CMP_UNITNO AND CMP.L_CMP_UNITNO = CVT.L_CMP_UNITNO AND CMP.L_CMP_STATCD != 'MOD' AND CMP.L_CMP_CMPCD NOT IN ('INC', 'EMTY') AND VST.D_VST_DATE BETWEEN 20010101 AND 20040512 AND VST.D_VST_STATUS = 'DN' GROUP BY DVT1.DRWLOC_ID, CMP.L_CMP_UNITNO, CMP.L_CMP_INSTID
GO
SELECT DISTINCT NAM.N_NAM_ID AS 'ID1', NAM.N_NAM_INSTID AS 'INSTID1', NAM.N_NAM_FNAME AS 'FNAME1', NAM.N_NAM_MINITIAL AS 'MINITIAL1', NAM.N_NAM_LNAME AS 'LNAME1', PER.N_PER_BIRTH AS 'BIRTH1', ADR.N_ADR_ADDR1 AS 'ADDR1', ADR.N_ADR_ADDR2 AS 'ADDR2', ADR.N_ADR_CITY AS 'CITY1', ADR.N_ADR_STATE AS 'STATE1', SUBSTRING(ADR.N_ADR_ZIP, 1,5) AS 'ZIP1', PER.N_PER_EMAIL AS 'EMAIL1', PER.N_PER_GENDER AS 'GENDER1', PHNA.N_PHN_AREACD AS 'AREAD1', PHNA.N_PHN_PREFIX AS 'PREFIXD1', PHNA.N_PHN_NUMBER AS 'NBRD1', PHNA.N_PHN_EXTENTN AS 'EXTD1', PHNB.N_PHN_AREACD AS 'AREAD2', PHNB.N_PHN_PREFIX AS 'PREFIXD2', PHNB.N_PHN_NUMBER AS 'NBRE2', PHNB.N_PHN_EXTENTN AS 'EXTD2', BTY.D_BTY_ABO AS 'ABO1', BTY.D_BTY_RHESUS AS 'RHI', VST.D_VST_DATE AS 'FIRST1', DV2.COUNT_VISITS AS 'COUNT', SUM(DTS.D_DTS_DONSUM) AS 'AWARD', ELG.D_ELG_RWBDTE AS 'ELIG1' --INTO Donor_Visit4 From Donor_Visit2 DV2 RIGHT OUTER JOIN DNR_DTS_DB_REC DTS ON DV2.COUNT_INSTID = DTS.D_DTS_INSTID RIGHT OUTER JOIN NAT_PER_DB_REC PER ON DV2.COUNT_INSTID = PER.N_PER_INSTID RIGHT OUTER JOIN DNR_BTY_DB_REC BTY ON DV2.COUNT_INSTID = BTY.D_BTY_INSTID RIGHT OUTER JOIN NAT_PHN_DB_REC PHNA ON DV2.COUNT_INSTID = PHNA.N_PHN_INSTID RIGHT OUTER JOIN NAT_PHN_DB_REC PHNB ON DV2.COUNT_INSTID = PHNB.N_PHN_INSTID RIGHT OUTER JOIN DNR_DTS_DB_REC DNT ON DV2.COUNT_ID = DNT.D_DTS_ID RIGHT OUTER JOIN NAT_PER_DB_REC PER1 ON DV2.COUNT_ID = PER1.N_PER_ID RIGHT OUTER JOIN DNR_BTY_DB_REC BTY1 ON DV2.COUNT_ID = BTY1.D_BTY_ID LEFT OUTER JOIN NAT_PHN_DB_REC PHNA1 ON DV2.COUNT_ID = PHNA1.N_PHN_ID RIGHT OUTER JOIN NAT_PHN_DB_REC PHNB1 ON DV2.COUNT_ID = PHNB1.N_PHN_ID LEFT OUTER JOIN NAT_PHN_DB_REC PHNA2 ON PHNA2.N_PHN_PHTYP = 'D' LEFT OUTER JOIN NAT_PHN_DB_REC PHNB2 ON PHNB2.N_PHN_PHTYP = 'E', --LEFT OUTER JOIN DNR_DTS_DB_REC DTS1 --DTS1.D_DTS_CNTTYP <> 'N', DNR_ELG_DB_REC ELG, NAT_NAM_DB_REC NAM, NAT_ADR_DB_REC ADR, DNR_VST_DB_REC VST WHERE DV2.COUNT_INSTID = VST.D_VST_INSTID AND DV2.COUNT_INSTID = ELG.D_ELG_INSTID AND DV2.COUNT_INSTID = N_NAM_INSTID AND DV2.COUNT_INSTID = N_ADR_INSTID AND DV2.COUNT_INSTID = VST.D_VST_INSTID --AND DV2.COUNT_INSTID = ELG.D_ELG_ID AND NAM.N_NAM_SEQNO = 0 AND VST.D_VST_DATE = (SELECT MIN(VSTB.D_VST_DATE) FROM DNR_VST_DB_REC VSTB WHERE VST.D_VST_INSTID = VSTB.D_VST_INSTID AND VSTB.D_VST_STATUS = 'DN' AND VST.D_VST_ID = VSTB.D_VST_ID) AND NOT EXISTS (SELECT R_DRC_ID FROM REC_DRC_DB_REC WHERE R_DRC_ID = COUNT_ID AND R_DRC_INSTID = COUNT_INSTID AND R_DRC_RESPCD = '15') GROUP BY NAM.N_NAM_ID, NAM.N_NAM_INSTID, NAM.N_NAM_FNAME, NAM.N_NAM_MINITIAL, NAM.N_NAM_LNAME, PER.N_PER_BIRTH, ADR.N_ADR_ADDR1, ADR.N_ADR_ADDR2, ADR.N_ADR_CITY, ADR.N_ADR_STATE, ADR.N_ADR_ZIP, PER.N_PER_EMAIL, PER.N_PER_GENDER, PHNA.N_PHN_AREACD, PHNA.N_PHN_PREFIX, PHNA.N_PHN_NUMBER, PHNA.N_PHN_EXTENTN, PHNB.N_PHN_AREACD, PHNB.N_PHN_PREFIX, PHNB.N_PHN_NUMBER, PHNB.N_PHN_EXTENTN, BTY.D_BTY_ABO, BTY.D_BTY_RHESUS, VST.D_VST_DATE, DV2.COUNT_VISITS, DTS.D_DTS_DONSUM, ELG.D_ELG_RWBDTE
Error Message:
(845 row(s) affected)
(844 row(s) affected)
(396 row(s) affected)
Server: Msg 9002, Level 17, State 6, Line 2 The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space. Server: Msg 1105, Level 17, State 1, Line 2 Could not allocate space for object '(SYSTEM table id: -109901351)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.
I am not a SQL programmer, but I am trying to use data collected in our Job Costing system to feed data to a crystal report that summarizes the current department name and the last date each job was scanned into a department. I used aliases of the Process table to extract the max date for each department.
This statement works fine, but sometimes it hangs and locks my process table. I am not sure what event causes the lock, but I think it has to do with the users aborting the report during the SQL extract or multiple users trying to report at the same time (8am when they first arrive at work).
I showed this to a consultant and he showed me that the MAX statement in the 3rd to last join was eating most of the execution time. Also he told me I am using too many joins and should look into Table Views. The statement completes in 15 to 45 seconds, depending on the workload.
What can I do to improve the performance of this code and to avoid the locks?
SELECT OH.JobNumber, OH.PlantID, OH.CreateOpr, OC.ComponentNumber, PJN.ProductionCode as ProductionMax, P2.ProcessCode as MaxCC, PS.Description AS MaxCCDesc, PJ1.ProductionCode, P.ProcessCode as ProdCC, P51.CreateDatim AS SchCCDate, P53.CreateDatim AS TypCCDate, P55.CreateDatim AS OPrCCDate, P57.CreateDatim AS HPrCCDate, P59.CreateDatim AS CRmCCDate, P61.CreateDatim AS CCeCCDate, P63.CreateDatim AS PRmCCDate, P65.CreateDatim AS BinCCDate, P67.CreateDatim AS JbOCCDate, P69.CreateDatim AS OnDCCDate, P71.CreateDatim AS ShpCCDate, PS1.Description AS CCDesc, P.CreateDatim AS CCDate, OQT.Quantity, OH.JobDescription, OH.FormNumber, OH.JobDescription, OH.USERDEFINED1 AS JobType, OH.CustAccount, OH.CustName, (select OrderHeader.DueDate from OrderHeader where OrderHeader.JobNumber = OH.JobNumber and OH.NoDueDate = 0) as DueDate, OH.ProofDate, OH.OrderDate, OH.SalesRepCode, OH.PONumber, OH.PrevPONumber, OH.NoDueDate, OC.UserDefined1, OC.Description as ComponentDescription FROM OrderComponent OC INNER JOIN OrderHeader OH ON OC.JobNumber = OH.JobNumber INNER JOIN OrderQtyTable OQT ON OC.JobNumber = OQT.JobNumber and OC.ComponentNumber = OQT.ComponentNumber and OC.QtyOrdIndex = OQT.QuantityLineNo LEFT JOIN ProductionJobNumber PJ1 ON PJ1.JobNumber = OH.JobNumber and PJ1.ComponentNumber = OC.ComponentNumber LEFT JOIN Production P ON PJ1.ProductionCode = P.Code LEFT JOIN Production P51 ON PJ1.ProductionCode = P51.Code AND P51.ProcessCode = 9151 LEFT JOIN Production P53 ON PJ1.ProductionCode = P53.Code AND P53.ProcessCode = 9153 LEFT JOIN Production P55 ON PJ1.ProductionCode = P55.Code AND P55.ProcessCode = 9155 LEFT JOIN Production P57 ON PJ1.ProductionCode = P57.Code AND P57.ProcessCode = 9157 LEFT JOIN Production P59 ON PJ1.ProductionCode = P59.Code AND P59.ProcessCode = 9159 LEFT JOIN Production P61 ON PJ1.ProductionCode = P61.Code AND P61.ProcessCode = 9161 LEFT JOIN Production P63 ON PJ1.ProductionCode = P63.Code AND P63.ProcessCode = 9163 LEFT JOIN Production P65 ON PJ1.ProductionCode = P65.Code AND P65.ProcessCode = 9165 LEFT JOIN Production P67 ON PJ1.ProductionCode = P67.Code AND P67.ProcessCode = 9167 LEFT JOIN Production P69 ON PJ1.ProductionCode = P69.Code AND P69.ProcessCode = 9169 LEFT JOIN Production P71 ON PJ1.ProductionCode = P71.Code AND P71.ProcessCode = 9171 LEFT JOIN Process PS1 ON P.ProcessCode = PS1.ProcessCode LEFT JOIN ProductionJobNumber PJN ON PJN.ProductionCode = (select MAX(ProductionJobNumber.ProductionCode) From ProductionJobNumber where OH.Jobnumber = ProductionJobNumber.JobNumber AND OC.ComponentNumber = ProductionJobNumber.ComponentNumber) LEFT JOIN Production P2 ON PJN.ProductionCode = P2.Code LEFT JOIN Process PS ON P2.ProcessCode = PS.ProcessCode WHERE OH.JobStatus = 'IN PROCESS'
What i want to happen is Table_A will hold a recid and an update field. within the if statement it needs to JOIN with Table_B ON recid and check the value of one/two possible fields to determine if that recid goes to Table_F or Table_G
I need to get this INNER JOIN embedded in a nested if along with more INNER JOINs
INSERT BACKFILE_AP SELECT WORK_INTRO.RECID, WORK_INTRO.UPDATED FROM WORK_INTRO INNER JOIN SIF_DT1 ON WORK_INTRO.RECID = SIF_DT1.RECID WHERE SIF_DT1.ROUTE_CODE = 'BACKFILE' DELETE WORK_INTRO FROM WORK_INTRO INNER JOIN SIF_DT1 ON WORK_INTRO.RECID = SIF_DT1.RECID WHERE SIF_DT1.ROUTE_CODE = 'BACKFILE'
Heya - Very new here to SQL, but when I do this following query
SELECT ProjMgr, SUM(Fee) AS Fee FROM dbo.PR WHERE (ProjMgr = '00138') GROUP BY ProjMgr
I return these results:
ProjMgrFee 001389145297
Simple enough, so I flex my newbie SQL muscles and put in the last name instead of the ProjectManager's employee number
SELECT dbo.EM.LastName, SUM(dbo.PR.Fee) AS Fee FROM dbo.PR INNER JOIN dbo.EM ON dbo.PR.ProjMgr = dbo.EM.Employee WHERE (dbo.PR.ProjMgr = '00138') GROUP BY dbo.EM.LastName
And get these results
LastNameFee Boulet9145297
Okay, NOW I'm getting cocky. I try to bring in an amount indicating held labor from another table that will have multiple instances of the same number, called WBS1, and show a sum of values contained over months of history in a sum. Before I do that, I add the table, and then I'm stuck - look what happens to my fee column...any ideas?
SELECT dbo.EM.LastName, SUM(dbo.PR.Fee) AS Fee FROM dbo.PR INNER JOIN dbo.EM ON dbo.PR.ProjMgr = dbo.EM.Employee INNER JOIN dbo.LD ON dbo.PR.WBS1 = dbo.LD.WBS1 WHERE (dbo.PR.ProjMgr = '00138') GROUP BY dbo.EM.LastName
Select * from Fact1 UNION Select * from Fact2 JOIN
(Select top(col1),... from Fact3 INNER JOIN Table1 group by Col2 ) ON....
The above one was executing in 5 minutes
I changed the inner query to say
Select Quarter,top(col1),... from Fact3 INNER JOIN View1 group by Quarter,Col2
I have made sure that the inner query is returning the same set of rows before and after modification. But still the modified query runs for ever. I am refering 2 databases(both SQL Server 2000 databases, on the same server- including the current). Please also note that I had a inner join to a table which I have changed to a view in the inner query.
I suppose this is due to the change in the execution plan. What should I do to make this work?
Any ideas?
Prakash.P The secret to creativity is knowing how to hide your sources!
I am very new to SQL and I have a question about inner joins. By using a lookup table and a row function, I have the following question and code. I am not sure if it right or not. I would appreciate any assistance:
For every type of food in the l_foods table, list the following:
The full name of the supplier from the l_suppliers table. The description of food from the l_foods table The price from the l_foods table plus the price_increase from the l_foods table; rename it to total_price
When price increase is null, assume that the 10 cents will be added to the price. Sort this information ty the total price.
SELECT a.supplier_name, b.description, b.price_increase AS total_price FROM l_suppliers AS a, l_foods AS b;
Orange- l_foods table Blue - l_supplier table Red - within both tables.