Is it going to be faster to join several tables together and then
select what I need from the set or is it more efficient to select only
those 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 the
same.
I need the fastest most efficient method to extract the data as this
query is one of the most used in the system.
I recently learnt that there is a different approach to create "Joins" whilst developing an ASP .Net application. Usually i have databases that have relationships (JOINS) created etc so i never did this e.g. if i wanted to retrieve data from another table i would have done something like this:
JOIN - What I use to do.... SELECT Products.ProductID, Products.ProductName, Products.SupplierID, Products.QuantityPerUnit, Products.UnitPrice, Products.UnitsInStock, Products.UnitsOnOrder, Products.ReorderLevel, Products.Discontinued, Categories.CategoryName, Suppliers.CompanyName, Products.CategoryID FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID
Today i learnt this:
Sub Query without JOINS SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued, (SELECT CategoryName FROM Categories WHERE (CategoryID = Products.CategoryID)) AS CategoryName, (SELECT CompanyName FROM Suppliers WHERE (SupplierID = Products.SupplierID)) AS SupplierName FROM Products
The second code (Sub query) seems to be easily readable but fairly difficult to create to a certain degree. So my question is which method should i use for development purposes? Is there any real difference or just ok to use whichever im comfortable with?
Fan of Sub's and I feel more comfortable doing them, I've been told they aren't as efficient as JOIN's by another developer. Is this true?
Also, does one or the other perform better on SQL Server vs. Oracle? I'm more seasoned as an Oracle developer and have learned to avoid JOIN's when ever possible. So I guess my real question is have I been led astray?
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.
FROM [db_db].[dbo].[TEST] TC join [NEW_DB].[dbo].[Users]users on users.FirstName=TC.Username join [New_DB]..[Method_Master] Mstr on Mstr.Description=TC.Method join [New_DB]..[TestSource] TS on TS.Name = TC.TestName
basically there is a join between three tables to repalce all the varchars to the corresponding ID.
Please guide me in how should I go about in designing my SSIS package to achieve this requirement.
I require to perfom a join on 3 tables within the same query . To explain myself better i have 3 tables
Main table
Label table
textbox table The Main table contains the common fields in both the label and textbox table. While the label and textox table contain the fields that are sepcfic to them .
MAIN Table
pk Moduleid ItemName itemtype
36 372 test1 4
37 372 test2 4
38 372 test3 4
39 372 test4 6
40 372 test5 4
label
pk Main_fk labeltext
4 36 labeltext1
5 37 labeltext2
6 38 labeltext3
7 40 labeltext4
Textbox
pk Main_fk textboxtext
1 39 textbox1
I did infact manage to perform a join on these these tables.
Select * From tb_Main
inner join tb_Label
on tb_Main.pk = tb_Label.main_fk
where moduleID = @moduleID
Select * From tb_Main
inner join tb_textbox
on tb_Main.pk = tb_textbox.main_fk
where moduleID = @moduleID
The problem is that it returns two separate results . I require a join on the label and textbox table within the same query to return one result.
Is what im asking possible? I would appreciate if some exmaples are posted
I have no control on the design of the tables as i didnt create them but still if anyone has a suggestion on improving them please do ,so i can tell my colleague that they aren't designed well !!!!
Is it possible to utilize more than two tables in a single outer join? I have one table that I want every row and 18 others where I only want an entry if one is present meeting the conditions of "1.customerid = 2.Customerid" etc. I haven't run across this before and would appreciate any help.
I have a general question concerning joins. Below is a table scenario:
SELECT * FROM TABLE_A T0 INNER JOIN TABLE_B T1 ON T1.[Some_Column] = T0.[Some Column] LEFT JOIN TABLE_C T2 ON T2.[Some_Column] = T0.[Some Column]
Does the above indicate that all records in common between TABLE_A & TABLE_B will be returned, then the records from TABLE_C will be joined to the initial 'result set' (that is the result of joining TABLE_A & TABLE_B), or will TABLE_C simply be joined to TABLE_A regardless of the inner join between TABLE_A & TABLE_B?
I am writing a download process in which i have a condition where ineed to join four tables. Each table have lot of data say around300000 recs.my question is when i am doing the joins on the columns is there anyspecific order i need to follow.for exampleMy original query looks like thisselect a.col1,a.col2from ainner join bon a.id1=b.id1and a.id2=b.id2inner join con c.id1=b.id1and c.id2=b.id2inner join don d.id1=c.id1and d.id2=c.id2If i change the query like below... does it make any differenceselect a.col1,a.col2from ainner join bon b.id1=a.id1and b.id2=a.id2inner join con c.id1=a.id1and c.id2=a.id2inner join don d.id1=a.id1and d.id2=a.id2Any help is appreciated.ThanksSri
I had a SP to generate a recordset for a MIS report. It had 11 temp tables , basically taking data from real tables , performing some aggregation and counts and passin on the records to a temp table which gave the result as desired ..ofcourse with some group by clauses...
Now i replaced these temp tables (most of them..left with just 2), and used derieved tables instead in the final query..and joins which will execute with each query iteration..something of the sort
select col1,co2 ,
(select count(id) from sometable x group by sayaccount where x.id = temp.id) ,
(select sum(id) from (select count(id) from sometable group by sayaccount) DERIVED_Tab),
......
from #finaltemp temp
group by col1.....
(earlier the count was stored in 1 temp table, then sum one n stored in other).
the idea was to reduce the execution time...but i din achieve it...not with just a single user running the report i.e , rather it marginally increased. my thinking was that i'll be avoiding the locks on tempdb by reducing the number of temp tables....pls tell me if im goin wrong...i still have the option of using table datatype if thats feasible..
Hi, all:This is probably a simple problem, but, as an SQL newbie, I'm having alittle trouble understanding multi-joins and subqueries.I have the following tables and columns:MemberTable-----MemberID (primary key)MemberNameAddressCountryFoodsTable------FoodID (primary key)FoodNameMusicTable-----MusicID (primary key)MusicNameMoviesTable-----MoviesID (primary key)MoviesName....and their linking tables...Members2FoodsTable-----MemberID (foreign key)FoodsID (foreign key)Members2MoviesTable-----MemberID (foreign key)MoviesID (foreign key)....and so forth.Now what I'm trying to do is retrieve a specific MemberID, his address info(from the Members table), and get a listing of his favorite Movies, Foods,Music, etc. I know I probably need to JOIN tables, but where do I JOIN thetables? Do I have to JOIN the various Music/Foods/Movies tables or is itthe Music/Members2Music and Foods/Members2Foods, etc. tables? And I assumeI would probably need to perform a subquery somewhere?I realize I'll need to first filter the Members, Members2Music,Members2Foods, etc. tables by the MemberID, and afterwards, retrieve alisting of the actual Music/Foods/Movies names. I'm just confused how to dothat. (By the way, I have a total of 10 other tables or in addition toMusic, Foods, etc. so it's a lot of table JOINing.)If someone could please help me out with the actual SQL coding, I'd reallyappreciate it!Thanks for the help!J
I have to write a report using SSRS using a cube somebody (no longer here) created. I know SSRS and SQL very well, but have never worked with cubes.
I want to make sure the cube was properly constructed (in terms of table/join relationships). I cannot find the tables/joins. I looked in SSMS under Data Source Views and everything else I could think of (or google).
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.
I'm new to ms-sqlserver ( 2000 ) and need to get an OUTER JOIN workingon a three table query.Assumptions:-- I have events in the Event table.-- Each event CAN have one Transaction, but it's not guaranteed-- Each transaction, ir present, will have one or more Amount recordsThis would be the pseudo-query without any special joins:-----------------------------------------SELECTa.Name,SUM( c.amount ) as TotalFROMEvent a,Transaction b,Amounts cWHEREa.EventID = b.EventIDANDb.TransID = c.TransID-----------------------------------------This is fine if there is a Transaction for the Event. But, if there'sno transaction for an event, no record is pulled of course.What I need is for a record to come back for each event regardless ofthe presence of a Transaction. If there's no transaction, then the"Total" column should be 0.How would I get an OUTER JOIN to work on this so that each Event gets arecord?TIA-BEP
Table 3: ID RoleID Time 1 1 09:14 2 1 09:15 1 2 09:16
Now I want all the records which belongs to RoleID 1 but if same ID is belongs to RoleID 2 than i don't want that ID.From above tables ID 1 belongs to RoleID 1 and 2 so i don't want ID 1.
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.
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'
