Logo

Advertisement

ANSI-92 Inner Join Vs. Where Clause Syntax

www.dbforums.com

Having problems rewriting my join condition using the "inner join" syntax. My query, working with an intersection table: SELECT Description, EmailAddress FROM Accounts_Roles r, Accounts_Users u, Accounts_UserRoles ur WHERE r.RoleID = ur.RoleID AND u.UserID = ur.UserID This works fine, but i want to write it using 'inner join' style, so I tried: SELECT Description, EmailAddress FROM Accounts_Roles r, Accounts_Users u INNER JOIN Accounts_UserRoles ur ON r.RoleID = ur.RoleID AND u.UserID = ur.UserID which gives me an error (The column prefix 'r' does not match with a table name or alias name used in the query.) Any ideas as to how I'm screwing this up would be appreciated. Thanks, Gordon Z

View Answers
Very similar questions

Advertisement

Whats Difference In Using Where And Inner Join ? www.vbcity.com

Whats the difference between where with = operator and inner join with = operator they both return same result so why use innerjoin instead of where clause?I need technical difference not like Inner join is sql ansi standard and so on.is join faster than where clause.for e.g the below query returns same result if you use join or where clauseMSDN Join referece SELECT P.ProductID,       S.SupplierID,       S.CompanyNameFROM Suppliers AS S JOIN Products AS P     ON (S.SupplierID = P.SupplierID)WHERE P.UnitPrice > $10  AND S.CompanyName LIKE N'F%'using where clauseSELECT P.ProductID,       S.SupplierID,       S.CompanyNameFROM Suppliers AS S, Products AS PWHERE S.SupplierID = P.SupplierID  AND P.UnitPrice > $10  AND S.CompanyName LIKE N'F%'Thanks Mind is faster than Wind --- Mahabharat

Question: Has Anyone Written A Tool To Convert From ANSI-89 To ANSI-92 Join Syntax? social.msdn.microsoft.com

�A question for everyone: With the introduction of SQL 2005, we now have to use ANSI-92 T-SQL Syntax and I was wondering if anyone had written a tool to convert queries from old ANSI SQL to the new syntax. We have some code that has to change for the outer joins, but we also have a lot of code that should change for the inner joins. It doesn't seem that difficult to write something that parses an old piece of code and at least suggests a new version. Especially if the conversion code wasn't SQL code. Thanks, in advance, Brian

Subquery Problem www.xtremevbtalk.com

i have a problem and find the solutionthis query does not workdelete from twdwagen where twid= ( SELECT twdwagen.twidFROM merk INNER JOIN (`type` INNER JOIN twdwagen ON type.typeid = twdwagen.typeid) ON merk.merkid = type.newmerkkeyWHERE (((merk.merkid)=1)))errorError Code : 1064You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT twdwagen.twidFROM merk INNER JOIN (`type` INNER JOIN tw(0 ms taken)when i try this, this worksSELECT twdwagen.twidFROM merk INNER JOIN (`type` INNER JOIN twdwagen ON type.typeid = twdwagen.typeid) ON merk.merkid = type.newmerkkeyWHERE (((merk.merkid)=1))thx

Ansi 92 Related www.access-programmers.co.uk

Hi All, I want to use Ansi-92 query format in access. When I use between function with '2006/06/10' (Ansi-92) it gives error I need to use # 2006/06/10 # (Ansi-89). I am using Access 2002 file format but still it dosent work. I have set the database to ANSI-92 format using options in tools. Can anyone help me in this? Regards. Dilip Apte

I Am Getting An Error In The FROM Clause www.xtremevbtalk.com

Can somebody help me out with this one? I am getting a syntax error in the FROM clause when I try to run this SQL statement in VB6.rs_costbydept.Open "SELECT * FROM Rates RIGHT JOIN(Departments INNER JOIN(Unions INNER JOIN AllEmployees ON Unions.UnionID = AllEmployees.UnionCode) ON Departments.DepartmentID = AllEmployees.DepartmentCode) ON Rates.Code = AllEmployees.ID) ORDER BY AllEmployees.Last, Rates.Type;", cn, adOpenKeyset, adLockPessimistic, adCmdTableCan anyone see anything wrong with it?Thanks in advance.

Syntax Error In Query To A Mysql Database visualbasic.ittoolbox.com

Hi all,I'm working on using a mysql db with a VB application.The following SQL query generates a syntax error.Anybody have an idea whats the error?The query is:SELECTSmyAwAr.Awakenings,SmyAwAr.NORM_Awakenings,SmyAwAr. WASOFROM ((SmyAwAr INNER JOIN Scorer ON SmyAwAr.ScorerIdScorer.ScorerId) INNER JOIN Type ON SmyAwAr.TypeIdType.TypeId) INNER JOIN (Sleep INNER JOIN Record ONSleep.RecordId = Record.RecordId) ON SmyAwAr.RecordId= Record.RecordId AND SmyAwAr.RecordIdSleep.RecordId WHERE Record.RecordId = 4 ANDScorer.ScorerId = 1 AND Type.TypeId = 1===============The error massage says that there is a syntax errornear "(Sleep INNER JOIN Record ON Sleep.RecordIdRecord.RecordId) ON SmyAwAr.RecordId" Regards,Madhavi

Sql Command - 2 Inner Joins www.codeguru.com

Not sure how to do this - keep getting a syntax errorTHIS WORKS (One Inner Join)Code:Select [ACCOUNT], [KEY], [Stock].[DESC], [GRP], [CLASS], [PRICE], [FROM_DATE], [TO_DATE], [TYPE_CD] from [SpecialPrices] INNER JOIN [Stock] ON [SpecialPrices].[KEY] = [Stock].[Key] ORDER BY [ACCOUNT] ASCI would like to do 2 Inner Joins so that I can have the Account Name alongside the Account NumberSo I presume it will look something like ...Code:Select [ACCOUNT], [Debtor].[Account_Name], [KEY], [Stock].[DESC], [GRP], [CLASS], [PRICE], [FROM_DATE], [TO_DATE], [TYPE_CD] from [SpecialPrices] INNER JOIN [Debtor] ON [SpecialPrices].[ACCOUNT] = [Debtor].[DR_KEY] INNER JOIN [Stock] ON [SpecialPrices].[KEY] = [Stock].[Key] ORDER BY [ACCOUNT] ASCThe added code is BOLD but gives a syntax error

INNER JOIN On Array www.webmasterworld.com

SELECT this.foo FROM this INNER JOIN that ON this.bar IN (that.baz) WHERE 1 If 'that.baz' is a comma separated list (like ན,54,67,92'), only rows that match the first element in the array (ན') will be returned. However, if I hardcode the array: SELECT this.foo FROM this INNER JOIN that ON this.bar IN (23,54,67,92) WHERE 1 then it works as expected. Any ideas?

Delete All Rows In Master Table If Identical In Second Table - SQL www.access-programmers.co.uk

A SELECT works: SELECT * from mastertable INNER JOIN secondarytable ON mastertable.Identicalfield = secondarytable.Identicalfield But none of these do: DELETE FROM mastertable INNER JOIN secondarytable ON mastertable.Identicalfield = secondarytable.Identicalfield **Incorrect syntax near the keyword 'INNER'.** DELETE FROM mastertable JOIN secondarytable ON (mastertable.Identicalfield = secondarytable.Identicalfield) **Incorrect syntax near the keyword 'JOIN'.** DELETE FROM mastertable WHERE (mastertable.Identicalfield = secondarytable.Identicalfield) **The column prefix 'secondarytable' does not match with a table name or alias name used in the query.** DELETE FROM mastertable WHERE IN (SELECT * FROM secondarytable) **Incorrect syntax near the keyword 'IN'.** Can anyone help? This should be easy, shouldn't it?! Thank you.

Asp - Syntax Error In FROM Clause stackoverflow.com

I'm working on asp.net web application & for this use MS access for back end, My Query is given below which is successfully executed on MS Access but error on front end ("Syntax Error in FROM Clause") select USER.EMPID as EMPID,USER.FULLNAME as FULLNAME, USER.USERNAME as USERNAME,Employee.ROLEID,ROLE.ROLENAME AS ROLE FROM USER inner join employee on user.userid=employee.userid inner join role on employee.roleid=role.roleid WHERE USER.EMAIL='admin@itiersolutions.com' AND USER.PASSWORD='cZdqAEeDV2EVzA1JNFJ6hQ==' AND USER.STATUS='Enable'

Advertisement

C# - Why Get An "Incorrect Syntax Near The Keyword INNER'' Error stackoverflow.com

In SQL Query Analyzer I run this statement and I don't get anny error: string select = "SELECT " + "aanvrager.werknemersnaam AS melder , hd_aanvragen.aanvraag_titel, " + "hd_aanvragen.aanvraag_omschrijving, hd_aanvraag_fase.fase_datum, " + "hd_melding_niveau_1.niveau_omschrijving AS 'Niveau 1', " + "hd_melding_niveau_2.niveau_omschrijving AS 'Niveau 2', " + "hd_aanvragen.outlook_id" + "FROM hd_aanvragen " + "INNER JOIN hd_meldingen ON hd_meldingen.melding_id = hd_aanvragen.melding_id " + "INNER JOIN hd_melding_niveau_1 ON hd_melding_niveau_1.niveau1_id = hd_meldingen.niveau1_id " + "INNER JOIN hd_melding_niveau_2 ON hd_melding_niveau_2.niveau2_id = hd_meldingen.niveau2_id " + "INNER JOIN hd_aanvraag_fase ON hd_aanvraag_fase.aanvraag_id = hd_aanvragen.aanvraag_id " + "INNER JOIN hd_statussen ON hd_statussen.status_id = hd_aanvraag_fase.status_id " + "INNER JOIN hd_werknemers AS oplosser ON oplosser.werknemer_Id = hd_aanvraag_fase.werknemer_Id " + "INNER JOIN hd_werknemers AS aanvrager ON aanvrager.werknemer_Id = hd_aanvragen.werknemer_Id " + "WHERE hd_statussen.status_id = 16"; But if I run this select-statement here: MyDataSource.SelectCommand = select; I get this error: Incorrect syntax near the keyword 'INNER'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'INNER'.

Using Outer Join In Multiple Table Query www.dbforums.com

What is the best way to use a left join in a SQL statement with multiple tables (more than 2)? I have a query that uses 7 tables, where most of the joins are inner joins, except for one, which needs to be a left join. The current SQL statement looks something like this: SELECT [table1].[field1], [table2].[field1], [table3].[field1], [table4].[field1], [table5].[field1], [table6].[field1], [table7].[field1] FROM [table1],[table2],[table3],[table4],[table5],[table6],[table7] WHERE [table4].[field2]=[table1.field2]{this is an inner join} [table4].[field2]=[table2.field2]{this is an inner join} [table4].[field2]=[table3.field2]{this is an inner join} [table4].[field2]=[table5.field2]{this is an inner join} [table5].[field3]=[table6.field2]{this is an inner join} [table5].[field4]=[table7.field2]{this is needs to be a left join} As it stands now, the last line in the WHERE clause is an INNER JOIN and limits the number of rows in my result. I need to select rows from [table7].[field2] whether or not a matching record exists in [table5].[field4]. The other INNER JOINS in the SQL statement must have matching records. Please advise.

Sql Query Syntax Error forums.asp.net

Ok I can run the query below in SQL Query Analyzer with no problems. However when I place the SQL query in my asp.net page I get a syntax error. It looks like there is some issue I am not seeing can someone help me. The error i get is "Incorrect syntax near 'pb_sub_recipes_1'. " I have narrowed it down to the area in bold as to where the syntax error appears to be occuring. SELECT DISTINCT pb_customers.customer_name FROM pb_sub_recipes AS pb_sub_recipes_1 INNER JOIN ((((((pb_jobs INNER JOIN pb_jobs_lots ON pb_jobs.job_id = pb_jobs_lots.job_id) INNER JOIN pb_recipes ON pb_jobs.recipe_id = pb_recipes.recipe_id) INNER JOIN pb_recipes_sub_recipes ON pb_recipes.recipe_id = pb_recipes_sub_recipes.recipe_id) INNER JOIN pb_customers ON pb_jobs.customer_id = pb_customers.customer_id) INNER JOIN pb_sub_recipes ON pb_recipes_sub_recipes.sub_recipe_id = pb_sub_recipes.sub_recipe_id) LEFT JOIN pb_report_shippers ON pb_jobs.job_id = pb_report_shippers.job_id) ON pb_sub_recipes_1.sub_recipe_id = pb_recipes_sub_recipes.sub_recipe_id WHERE (((pb_jobs.date_time)> '5/30/2004') AND pb_customers.customer_id ='228' AND ((pb_report_shippers.shipper_date_time) Is Null) AND ((pb_jobs.job_deleted)=0)) GROUP BY pb_customers.customer_name, pb_jobs.date_time, pb_sub_recipes.energy,pb_sub_recipes.dose,pb_jobs.job_id,pb_sub_recipes.specie,pb_sub_recipes_1.cost_per_wafer, pb_sub_recipes_1.setup_cost pb_sub_recipes_1.wafers_in_batch"

Ansi Joins Vs. SQL Joins forums.databasejournal.com

Hi, 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. Angel

Query About Ansi-Syntax www.dbforums.com

I need to write a stored procedure where I need to joing 3 tables A, B and C having approximately 200K, 500K, 800K rows respectively. Query: 1) If I use ansi-syntax (inner join) as against non-ansi syntax (A.col1 = B.col1), I get a better performance. Any idea why? 2) If I write a query (shown below), it tries to join table A and B returning large number of rows. Select A.Col1, A.Col2 from A, B where A.Col3 = 'xyz' Why does it try to join the table B with A though there is no join specified.

Worked In 2000 But Not In 2005 www.sqlteam.com

Hi there, I have a query with a rather large (46 table) 1:1 join. Technically, I'm creating a view then trying to select from that view. I seem to be able to create the view ok but cannot resolve a select from it. The error message is: Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. Msg 191, Level 15, State 1, Line 1 Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries. Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'WHERE'. This worked in 2000 without a problem Here is the join: FROM [HUSKY].USEP0712E.dbo.AGAF_us T1 INNER JOIN [HUSKY].USEP0712E.dbo.AGAM_us T2 ON T1.CODE = T2.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGASN_us T3 ON T1.CODE = T3.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGBF_us T4 ON T1.CODE = T4.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGBLK_us T5 ON T1.CODE = T5.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGBM_us T6 ON T1.CODE = T6.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGHF_us T7 ON T1.CODE = T7.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGHM_us T8 ON T1.CODE = T8.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGHSP_us T9 ON T1.CODE = T9.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGIF_us T10 ON T1.CODE = T10.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGIM_us T11 ON T1.CODE = T11.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGIND_us T12 ON T1.CODE = T12.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGNH_us T13 ON T1.CODE = T13.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGNHF_us T14 ON T1.CODE = T14.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGNHM_us T15 ON T1.CODE = T15.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGOF_us T16 ON T1.CODE = T16.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGOM_us T17 ON T1.CODE = T17.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGOTH_us T18 ON T1.CODE = T18.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGWF_us T19 ON T1.CODE = T19.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGWHT_us T20 ON T1.CODE = T20.CODE INNER JOIN [HUSKY].USEP0712E.dbo.AGWM_us T21 ON T1.CODE = T21.CODE INNER JOIN [HUSKY].USEP0712E.dbo.WH_AOH_us T22 ON T1.CODE = T22.CODE INNER JOIN [HUSKY].USEP0712E.dbo.WNH_AONH_us T23 ON T1.CODE = T23.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGAF_us T24 ON T1.CODE = T24.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGAM_us T25 ON T1.CODE = T25.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGASN_us T26 ON T1.CODE = T26.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGBF_us T27 ON T1.CODE = T27.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGBLK_us T28 ON T1.CODE = T28.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGBM_us T29 ON T1.CODE = T29.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGHF_us T30 ON T1.CODE = T30.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGHM_us T31 ON T1.CODE = T31.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGHSP_us T32 ON T1.CODE = T32.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGIF_us T33 ON T1.CODE = T33.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGIM_us T34 ON T1.CODE = T34.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGIND_us T35 ON T1.CODE = T35.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGNH_us T36 ON T1.CODE = T36.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGNHF_us T37 ON T1.CODE = T37.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGNHM_us T38 ON T1.CODE = T38.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGOF_us T39 ON T1.CODE = T39.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGOM_us T40 ON T1.CODE = T40.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGOTH_us T41 ON T1.CODE = T41.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGWF_us T42 ON T1.CODE = T42.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGWHT_us T43 ON T1.CODE = T43.CODE INNER JOIN [HUSKY].USEP0712P.dbo.AGWM_us T44 ON T1.CODE = T44.CODE INNER JOIN [HUSKY].USEP0712P.dbo.WH_AOH_us T45 ON T1.CODE = T45.CODE INNER JOIN [HUSKY].USEP0712P.dbo.WNH_AONH_us T46 ON T1.CODE = T46.CODE

DLookUp In Query Issues www.access-programmers.co.uk

Can anyone decipher what I'm doing wrong with the following syntax: SELECT tblEnrollment.EnrollmentID, tblEvalTypes.Abbreviation, tblOptionsBF.Abbreviation, tblEvalResults.EvalDate, tblEvalResults.FlightExaminer, DLookUp("[LastName]","tblMembers","[tblMembers].[MemberID]='" & [tblEvalResults].[FlightExaminer] & "'") AS FE, tblGrades.Abbreviation, tblEvalResults.EPE FROM tblEvalTypes INNER JOIN (tblGrades INNER JOIN (tblOptionsBF RIGHT JOIN ((tblMembers INNER JOIN tblEnrollment ON tblMembers.MemberID = tblEnrollment.MemberID) INNER JOIN tblEvalResults ON tblEnrollment.EnrollmentID = tblEvalResults.EnrollmentID) ON tblOptionsBF.OptionsBFID = tblEvalResults.BoldfaceID) ON tblGrades.GradesID = tblEvalResults.GradeID) ON tblEvalTypes.EvalTypesID = tblEvalResults.EvalTypesID WHERE (((tblEnrollment.EnrollmentID)=[Forms]![frmHome]![EnrollmentID])); The problem child is: DLookUp("[LastName]","tblMembers","[tblMembers].[MemberID]='" & [tblEvalResults].[FlightExaminer] & "'") AS FE Thanks!

ETL Transformation Query JOINS www.dbforums.com

How do I make this to work. Optimize the current query. Should I move all the joins from FROM to the WHERE clause?? My query isn't working as is. Any suggestion. Thanks!! ************************************************** **************** * * TRANSFORMATION 1A * Description: This Transformation will be ran for Contract Level Invoices * (i.e. Commodity, Reservation) * ************************************************** **************** ************************************************** ***************/ SELECT STGInvoice.companyNumber, ContractDim.contractKey, ShipperDim.shipperKey, PayerDim.payerKey, PayeeDim.payeeKey, PointDimRec.pointKey, PointDimDel.pointKey, DateDim.dateKey, MonthDimProd.monthKey, MonthDimAcct.monthKey, STGInvoice.invoiceTypeCode, STGInvoice.invoiceNumber, STGInvoice.invoiceDate, STGInvoice.invoiceDueDate, STGBaseChargeLineItem.lineItemAmount, STGBaseChargeLineItem.lineItemQuantity, (CASE STGBaseChargeLineItem.updateUserId WHEN 'SYSTEM' THEN 'S' ELSE 'M' END), STGBaseChargeLineItem.chargeCode, STGChargeType.LongDescription, STGBaseChargeLineItem.transactionTypeCode, STGRateVolumeType.longDescription, STGBaseChargeLineItem.chargeRate, STGBaseChargeLineItem.discountRate, (STGBaseChargeLineItem.chargeRate - STGBaseChargeLineItem.discountRate), STGBaseChargeLineItem.reversalFlag, STGBaseChargeLineItem.sequenceId, STGBaseChargeLineItem.relatedContractNum, (CASE WHEN STGBaseChargeLineItem.lineItemQuantity >= 0 THEN 'A' ELSE 'R' END), (CASE STGInvoice.invoiceTypeCode WHEN 'COMMODITY' THEN 'C' WHEN 'RESERVATION' THEN 'R' ELSE 'O' END), STGBaseChargeLineItem.volumeSourceCode, STGBaseChargeLineItem.baseChargeLineItemSID, 'GAS', STGInvoice.finanicalInvoiceId, STGInvoice.invoiceStatusTypeCode, GETDATE(), 'A' FROM STGInvoice INNER JOIN STGBaseChargeLineItem ON STGInvoice.invoiceNumber = STGBaseChargeLineItem.invoiceNumber INNER JOIN STGChargeType ON STGChargeType.chargeCode = STGBaseChargeLineItem.chargeCode AND STGChargeType.companyNumber = STGBaseChargeLineItem.companyNumber INNER JOIN STGRateVolumeType ON STGRateVolumeType.transactionTypeCode = STGBaseChargeLineItem.transactionTypeCode AND STGRateVolumeType.companyNumber = STGBaseChargeLineItem.companyNumber INNER JOIN STGAccountingMonth ON STGAccountingMonth.accountingMonth = STGInvoice.accountingMonth AND STGAccountingMonth.companyNumber = STGInvoice.companyNumber INNER JOIN ContractDim ON ContractDim.contractNum = STGInvoice.primaryEntityId AND ContractDim.companyNum = STGInvoice.companyNumber AND STGInvoice.primaryEntityTypeCode = 'CONTRACT' AND ContractDim.recordStatusCode = 'A' INNER JOIN ShipperDim ON ShipperDim.legalEntityNum = ContractDim.legalEntityNum AND ShipperDim.contactNum = ContractDim.shipperContactNum AND ShipperDim.companyNum = ContractDim.companyNum AND ShipperDim.recordStatusCode = 'A' /************************************************** ****** *Need to JOIN ShipperDim from above to PayerDim below using WHERE * How would I join HERE with a WHERE clause *NEED help with joins and optimization!! ************************************************** ******/ INNER JOIN PayerDim ON PayerDim.legalEntityNum = ContractDim.payerLegalEntityNum AND PayerDim.companyNum = STGBaseChargeLineItem.companyNumber AND PayerDim.purposeCode = 'IN' AND PayerDim.recordStatusCode = 'A' INNER JOIN PayeeDim ON PayeeDim.companyNum = STGBaseChargeLineItem.companyNumber AND PayeeDim.recordStatusCode = 'A' INNER JOIN PointDim PointDimRec ON PointDimRec.pointNumber = STGBaseChargeLineItem.receiptPointNumber AND PointDimRec.companyNum = STGBaseChargeLineItem.companyNum AND PointDimRec.recordStatusCode = 'A' INNER JOIN PointDim PointDimDel ON PointDimDel.pointNumber = STGBaseChargeLineItem.deliveryPointNumber AND PointDimDel.companyNum = STGBaseChargeLineItem.companyNum AND PointDimDel.recordStatusCode = 'A' INNER JOIN DateDim ON DateDim.dateStamp = STGBaseChargeLineItem.startDate INNER JOIN MonthDim MonthDimProd ON MonthDimProd.monthNumber = MONTH(STGInvoice.startDate) AND MonthDimProd.yearNumber = YEAR(STGInvoice.startDate) INNER JOIN MonthDim MonthDimAcct ON MonthDimAcct.monthNumber = MONTH(STGAccountingMonth.startDate) AND MonthDimAcct.yearNumber = YEAR(STGAccountingMonth.startDate) --appreciate any help!

Convert ANsi-89 To Ansi-92 Outer Join social.msdn.microsoft.com

I've been using this syntax for years on SQL Server and now comes the time to convert to SQL 2005 (90 compatibility).� This syntax returns four rows.� Basically it returns one row for each servername/component/context/property/value even when there does not exist a property of 'fff' since it's a left join: Code Block select t1.* from tblconfiguration t1 ,tblconfiguration t2 where t1.component = 'AdjProcessUtility' and t1.servername *= t2.servername and t1.component *= t2.component and t1.context *= t2.context and t1.property = 'proc' and t2.property = 'fff' � � Result: SQLEDEV1�AdjProcessUtility�DuplicatesReport�Proc�Adjustment.dbo.prcDuplicatesReport SQLEDEV1�AdjProcessUtility�ExtractAdjFile�Proc�Adjustment.dbo.prcAdjExtractMFFiles SQLEDEV1�AdjProcessUtility�ValidationProcess�Proc�prcAdjValidations SQLEDEV1�AdjProcessUtility�ValidationReport�Proc�Adjustment.dbo.prcValidationReport � When the converted (using SQL enterprise Mgr) runs�it returns no rows: Code Block SELECT���� t1.* FROM�������� dbo.tblConfiguration t1 LEFT OUTER JOIN ��������������������� dbo.tblConfiguration t2 ON t1.ServerName = t2.ServerName AND t1.Component = t2.Component AND t1.Context = t2.Context WHERE���� (t1.Component = 'AdjProcessUtility') AND (t1.Property = 'proc') AND (t2.Property = 'fff') � � � I don't really see how to change this query to make it work.� I've searched the web and I really don't see any examples of left joins which use more than one column. � Here's the table definition: Code Block CREATE TABLE dbo.tblConfiguration ( �ServerName VARCHAR(30)�� NOT NULL, �Component� VARCHAR(255) NOT NULL, �Context��� VARCHAR(255) NOT NULL, �Property�� VARCHAR(255) NOT NULL, CONSTRAINT PK_tblConfiguration PRIMARY KEY NONCLUSTERED( ServerName, Component, Context, Property ), �Value����� VARCHAR(255) NOT NULL ) � � I use this table to define reports and there attribues.� The rows repeat themselves except for the Property and Value columns Here is some of the data: � �SQLEDEV1�AdjProcessUtility�ExtractAdjFile�Proc�Adjustment.dbo.prcAdjExtractMFFiles �SQLEDEV1�AdjProcessUtility�ExtractAdjFile�RunTime�13:25 �SQLEDEV1�AdjProcessUtility�ExtractAdjFile�Schedule�2,3,4,5,6 �SQLEDEV1�AdjProcessUtility�ExtractAdjFile�FixedRecLength�71 �SQLEDEV1�AdjProcessUtility�ExtractAdjFile�WriteFileHeader�Y �SQLEDEV1�AdjProcessUtility�ExtractAdjFile�WriteTempTable�Y � �SQLEDEV1�AdjProcessUtility�ValidationProcess�Proc�prcAdjValidations �SQLEDEV1�AdjProcessUtility�ValidationReport�ReportClass�ReportCSV �SQLEDEV1�AdjProcessUtility�ValidationReport�Ids�Validation �SQLEDEV1�AdjProcessUtility�ValidationReport�RunTime�15:06 �SQLEDEV1�AdjProcessUtility�ValidationReport�Schedule�2,3,4,5,6 �SQLEDEV1�AdjProcessUtility�ValidationReport�DefaultFileName�Adj_ValidationReport_MMDDYYHHMM.csv � etc. � Any help is greatly appreciated, Sid

Sql QUry To ACcess 2k W/no Results www.xtremevbtalk.com

SELECT dbo.tblIRC_QS_Promo.QSNumber, dbo.tblIRC_QS_Promo.PromoWhere, dbo.tblIRC_QS_Promo.PromoDate, dbo.tblIRC_QS_Category.CatName, dbo.tblIRC_CallLog.MemNo, dbo.[tblLOOKUP-States].StateName, dbo.tblIRC_QS_Files.Dept, dbo.tblIRC_QS_Files.ShowOnReportFROM dbo.tblIRC_QS_Promo INNER JOIN dbo.tblIRC_CallLog ON dbo.tblIRC_QS_Promo.IDKey = dbo.tblIRC_CallLog.IDKey INNER JOIN dbo.tblIRC_QS_Files ON dbo.tblIRC_QS_Promo.QSNumber = dbo.tblIRC_QS_Files.QSNumber INNER JOIN dbo.tblIRC_QS_Category ON dbo.tblIRC_QS_Files.Catid = dbo.tblIRC_QS_Category.Catid INNER JOIN dbo.[tblLOOKUP-States] ON LEFT(dbo.tblIRC_CallLog.MemNo, 2) = dbo.[tblLOOKUP-States].IDWHERE (dbo.tblIRC_QS_Files.ShowOnReport = 'Y')I dont see any issues with it yet its returning no results. Is my syntax or strucutre wrong??

Inner Join Vs Where www.xtremevbtalk.com

In SQL Books online I don't see any difference between the clause where and the clause inner join.Is there an issue with inner join if the format is not the same between the 2 compared fields .. like if 1 field is string(30) and the other is string(32) is there a problem ?

Showing Null Values From ON Inner Join Sproc CLAUSE forums.asp.net

Hi all My query has some inner joins to some tables. And problem is when any ON clause get null as value, the correspondent record is not displayed. SELECT TableA.A, TableB.A FROM TableA INNER JOIN TableB ON TableA.A = TableB.A What I did try: SELECT TableA.A, TableB.A FROM TableA INNER JOIN TableB ON TableA.A = TableB.A OR TableA.A IS NULL (but It generates redundant values from TableB) I need to show all values even that value from Tablea is null Thank a lot for any help

Problem With Joining Tables www.xtremevbtalk.com

I'm trying to join 3 tables through the Query Builder in the VB Studio.The problem is that the generated code gives a Syntax Error. Here is the code:SELECT tblFlights.flightId, tblMemberFlights.memberId, tblMembers.lastName, tblMembers.firstNameFROM tblFlights INNER JOIN tblMemberFlights ON tblFlights.flightId = tblMemberFlights.flightId INNER JOIN tblMembers ON tblMemberFlights.memberId = tblMembers.memberIdSo I've decided to do same Query in Access and that's what I came up with:SELECT tblFlights.flightId, tblMemberFlights.memberId, tblMembers.firstName, tblMembers.lastNameFROM tblFlights INNER JOIN (tblMemberFlights INNER JOIN tblMembersON tblMemberFlights.memberId=tblMembers.memberId)ON tblFlights.flightId=tblMemberFlights.flightIdEverything is fine when I put this query into the Query Builder and check the Query Syntax but as soon as I click anywhere else the parentheses are automatically deleted and I get invalid SQL statement, i.e:SELECT tblFlights.flightId, tblMemberFlights.memberId, tblMembers.firstName, tblMembers.lastNameFROM tblFlights INNER JOIN tblMemberFlights INNER JOIN tblMembersON tblMemberFlights.memberId=tblMembers.memberId ON tblFlights.flightId=tblMemberFlights.flightIdCan anyone suggest any way I can get around the problem of disappearing parentheses?

Inner Join www.codeguru.com

When I try to Query from two tables which has a many to many relation from MSAcess, I get a SQL statement which has a Inner join. I don't fine any difference between the Inner Join and a where clause. Is this SQL query with inner join compatible with other Data Bases.

VB 6 - Run-Time Error 3075 Syntax Error (Missing Operator) In Query Expression www.xtremevbtalk.com

Hi allsorry for the inconveniences, I just wanna get this syntax thing out of my back.I modified my vb module and now I'm getting "Run-Time error 3075 syntax error(Missing operator) - query expression"Then it displayed this line of code as the problem line:Code:'TblPersonnel.[Name] = TblRequests.Personnel1 Union AllSelect TblRequests.ProjectID'.Below is the complete module:Code:DbPath = "\515opsisdallf00008SHAREDPyrlAppsPTStest2"DbName = "PTS.mdb"Set Db = OpenDatabase(DbPath & DbName)strSQL = "Insert Into TblProgrammersHours (ProjectID,ProgrammerInitials, CurrProgHrs, PrevProgHrs, TotalProgHrs, Positions)"strSQL = strSQL & " Select TblRequests.ProjectID, TblPersonnel.Initials, TblRequests.Prog1hrs, TblRequests.PrevProg1Hrs, TblRequests.TotalProg1hrs, 1 As Position " _& "From TblRequests Inner Join tblPersonnel On TblPersonnel.[Name] = TblRequests.Personnel1 "strSQL = strSQL & "Union All " _& " Select TblRequests.ProjectID, TblPersonnel.Initials, TblRequests.Prog2hrs, TblRequests.PrevProg2Hrs, TblRequests.TotalProg2hrs, 2 " _& "From TblRequests Inner Join tblPersonnel On TblPersonnel.[Name] = TblRequests.Personnel2 "strSQL = strSQL & "Union All " _& " Select TblRequests.ProjectID, TblPersonnel.Initials, TblRequests.Prog3hrs, TblRequests.PrevProg3Hrs, TblRequests.TotalProg3hrs, 3 " _& "From TblRequests Inner Join tblPersonnel On TblPersonnel.[Name] = TblRequests.Personnel3 "strSQL = strSQL & "Union All " _& " Select TblRequests.ProjectID, TblPersonnel.Initials, TblRequests.Prog4hrs, TblRequests.PrevProg4Hrs, TblRequests.TotalProg4hrs, 4 " _& "From TblRequests Inner Join tblPersonnel On TblPersonnel.[Name] = TblRequests.Personnel4 "strSQL = strSQL & "Union All " _& " Select TblRequests.ProjectID, TblPersonnel.Initials, TblRequests.Prog5hrs, TblRequests.PrevProg5Hrs, TblRequests.TotalProg5hrs, 5 " _& "From TblRequests Inner Join tblPersonnel On TblPersonnel.[Name] = TblRequests.Personnel5 "strSQL = strSQL & "Union All " _& " Select TblRequests.ProjectID, TblPersonnel.Initials, TblRequests.Prog6hrs, TblRequests.PrevProg6Hrs, TblRequests.TotalProg6hrs, 6 " _& "From TblRequests Inner Join tblPersonnel On TblPersonnel.[Name] = TblRequests.Personnel6 "strSQL = strSQL & ") As NewProgHrsTbl"Call Db.Execute(strSQL)Thanks.tgifgemini

Update & Inner Join How? www.sqlteam.com

Best Greetings, i want to make an update query for a table but the where clause will check a join with other talbe update table1 inner join table 2 on x.table1=y.table2 and y.table1= y.table2 set z='anything' or shall it be update table1 set z='anything' where x in (select x from table2) and y in (select y from table2) i dont know how the syntax be any idea plz ,also table 2 it will be derived from table1 in the form of select x,max(y) from table1 order by x

Joining Tables. Need Help www.codeguru.com

I have 2 tables in an Access database and am trying to join them.I am writing an employee time clock program that pulls the employee Id from one table (Associate) , but need to write the time in(out) to the other table(Time). I am using the statementSQLQuery = "SELECT * FROM Associate INNER JOIN Time ON Time.EmpId=Associate.EmpId"My error reads syntax in From clause.The Assoicate table opens up fine in the form load, but the Time table does not.Please help

Syntax Error Converting Varchar Value '/' To A Column Of Datatype Int forums.devshed.com

Hi, Can anyone please help me with the syntax for this query please. Error is "syntax error converting varchar value '/' to a column of datatype int" Query: Code: select iCalls_Calls.Call_ID,iCalls_Calls.Requestor,Type,Scope,iCalls_Calls.Status_ID,iCalls_Status.Status_I D, iCalls_Status.Status_Label,((select Count(*) from iCalls_Events where Call_ID = " & Session("Call_ID") & " ) + ' /' + ( select Count(*) from iCalls_Events where Call_ID = "& Session("Call_ID") & " and Events_Flag <> 0)) as Countrec from ((iCalls_Calls inner join iCalls_Status on iCalls_Calls.Status_ID=iCalls_Status.Status_ID ) inner join iCalls_Users on iCalls_Calls.Requestor=iCalls_Users.User_ID) left outer join iCalls_Messages on iCalls_Calls.Call_ID=iCalls_Messages.Call_ID where Requestor='" & Session("User_ID") & "' AND iCalls_Calls.Status_ID <> 6 order by iCalls_Calls.Call_ID Thanks...

Sql Left Join/inner Join Error www.xtremevbtalk.com

Hi ..I keep getting err.number -2147217900 syntax error(missing operator)....Im using vb to interface w/an access 2k db through Ado.I started off using inner joins then I realized as I broke up my db(perhaps too much to avoid nulls and such) that my pk [Employee Id] may ot be in all tbales and would cause an error. So I moved to a left join statement: FullRsQuery = "SELECT * FROM Employees LEFT JOIN WorkInfo ON " & _ "Employees.[Employee ID] = WorkInfo.[Employee ID] LEFT JOIN " & _ "Employees ON Emergency.[Employee ID] = Employees.[Employee ID] " & _ "Left JOIN Notes ON Employee.[Employee ID] = Notes.[Employee ID] " & _ "WHERE Employees.[Employee ID] = '" & EmpID & _ "' Order by Employees.[Employee ID] Asc"now I realize vb/sql/access converion may be screwy at times do I treid to pass my empIs variable with & without the ' ' around it. made no difference.Query analyzer I used at work told me this code was working but my db is at home..I Even took out everyting from the where clause on so the error is genereated b4 my where clause..any ideas?..oh here's my immediate window output:SELECT * FROM Employees LEFT JOIN WorkInfo ON Employees.[Employee ID] = WorkInfo.[Employee ID] LEFT JOIN Employees ON Emergency.[Employee ID] = Employees.[Employee ID] Left JOIN Notes ON Emergency.[Employee ID] = Notes.[Employee ID] WHERE Employees.[Employee ID] = '1' Order by Employees.[Employee ID] Asc

Help Please With Multiple Outer Joins To Return All Records From Main Table www.access-programmers.co.uk

I have this query in design view and in an asp page and it works fine: SELECT dbo_feedback.*, dbo_origin.originName, dbo_product.prodname, dbo_category.catName FROM dbo_product INNER JOIN (dbo_origin INNER JOIN ((dbo_feedback INNER JOIN (dbo_category INNER JOIN links_cat ON dbo_category.catID = links_cat.CatID) ON dbo_feedback.id = links_cat.FeedbackID) INNER JOIN links_product ON dbo_feedback.id = links_product.FeedbackID) ON dbo_origin.originID = dbo_feedback.origin) ON dbo_product.prodID = links_product.ProductID; BUT, I want to return all feedback entries, even if they have no matching Product or Category. :confused: When I change the inner joins to outer joins I get a syntax error in the browser window. I changed the join type in the relationship diagram in Access and tried to recreate this in query designer, but Access says the statement cannot be executed because of ambiguous outer joins. FYI, one feedback can have many products, many categories, and only one origin. I have the joins correct and enforced. Please help, thanks!!!

Joining Tables In ACCESS www.vbforums.com

is it possible to join tables in vb that accesses an ACCESS db by using the WHERE clause to join tables instead of the INNER JOIN clause? if i can , what is the difference ? thanks

Parameterized Order By Clause: Doesn't Work bytes.com

Can someone tell me why SQL seems to ignore my order by clause? I tried to run through the debugger, but the debugger stops at the select statement line and then returns the result set; so, I have no idea how it is evaluating the order by clause. THANK YOU! CREATE proc sprAllBooks @SortAscend varchar(4), @SortColumn varchar(10) as If @SortAscend = 'DESC' Select titles.title_id, title, au_lname, au_fname, Convert(varchar(12), pubdate, 101) as PubDate from authors inner join titleauthor on authors.au_id = titleauthor.au_id inner join titles on titleauthor.title_id = Titles.title_id ORDER BY au_lname CASE @SortColumn WHEN 'title' THEN title END, CASE @SortColumn WHEN 'au_lname' THEN au_lname END, CASE @SortColumn WHEN 'PubDate' THEN PubDate END DESC ELSE Select titles.title_id, title, au_lname, au_fname, Convert(varchar(12), pubdate, 101) as PubDate from authors inner join titleauthor on authors.au_id = titleauthor.au_id inner join titles on titleauthor.title_id = Titles.title_id ORDER BY CASE @SortColumn WHEN 'title' THEN title END, CASE @SortColumn WHEN 'au_lname' THEN au_lname END, CASE @SortColumn WHEN 'PubDate' THEN PubDate END GO

Making A Query More Efficient www.xtremevbtalk.com

Hello,I've got an SQL join query running over a MS Access DB with ADO 2.5. The query is quite complicated and basically, I want just some of the resulting rows. So I wrote something likeCode:SELECT * FROM (Table1 INNER JOIN Table2 ON Table1.ID= Table2.Key) INNER JOIN Table3 ON Table2.ID=Table3.Key) WHERE Table1.Something=ValueWell, I would like to make it more efficient in the sense that it does not produce all rows and then select just that with Something=Value, but first reduce the number of rows selected from Table1 and then perform the JOIN operations.I tried thisCode:SELECT * FROM ((SELECT * FROM Table1 WHERE Table1.Something=Value) INNER JOIN Table2 ON Table1.ID= Table2.Key) INNER JOIN Table3 ON Table2.ID=Table3.Key)andCode:SELECT * FROM (Table1 INNER JOIN Table2 ON Table1.ID= Table2.Key WHERE Table1.Something=Value) INNER JOIN Table3 ON Table2.ID=Table3.Key)but none of these works. Access complains about syntax errors etc.How do I reduce the number of rows BEFORE the JOIN?Thanks

Help SQL Query Needed www.xtremevbtalk.com

I am trying to run a query against an access db, but keep getting the following error msg:Syntax Error (missing operator) in query expressionHere is the query:Set oConn = CreateObject("ADODB.Connection") oConn.Open sConnSet oRS = CreateObject("ADODB.Recordset")oRS.Open "SELECT product.name,prod_amt.amount From transact " & _ "INNER JOIN prod_rel ON (transact.transactionid = prod_rel.transactionid) " & _ "INNER JOIN prod_amt ON (prod_rel.amountid = prod_amt.amountid) " & _ "INNER JOIN product ON (prod_rel.productid = product.productid) " & _ "Where transact.TransactionID = 1", oConn, 3 'adOpenStatic=3Any help would be greatly appreciated. Thanks!

SQL Server :: How To Update Query In SQL forums.asp.net

i am having problems updating my user_tbl and i was wondering if anyone could hely me with the syntax. I am only new to this. Here is my code : UPDATE User_tbl SET User_tbl.user_credit_limit = user_credit_limit - '" + txtCalender.Text + "' * product_price FROM User_tbl INNER JOIN Order_tbl ON User_tbl.users_id = Order_tbl.customer_id INNER JOIN Order_details_tbl ON Order_tbl.order_id = Order_details_tbl.order_id INNER JOIN Product_tbl ON Order_details_tbl.product_id = Product_tbl.product_id WHERE users_id = (select username LIKE '"+ txtUser1.Text.Trim() +"')

Query Performance social.msdn.microsoft.com

The below query seems to be very slow : select distinct a.* from test a inner join test1 b on b.col1 = a.col1 inner join test2 c on c.col2 = a.col2 where exists (select NULL from test3 d where (d.col3 = a.col3 or a.col3 is null)) All the columns involved in the WHERE clause and JOIN conditions have index. Is there� any alternative available for the above which can increase the performance ? Please advice, Thanks, Smitha

Syntax Error(missing Operator) www.vbforums.com

I tried executing the following query,but it returned to me an error message:"Syntax error(missing operator) in query expression Btest.BID=Bintervallist.BID inner Join BClient on Btest.BClientID=BClient.BClientID"I am using VB6 and access databaseVB Code:rs1.Open "Select * from Btest inner Join Bintervallist on Btest.BID=Bintervallist.BID inner Join BClient on Btest.BClientID=BClient.BClientID where Btest.BDate=#" & fdate & "#", cn, adOpenKeyset, adLockPessimistic thanks

OleDbDataAdapter.Fill Error? www.vbforums.com

I am trying to create a Web service and I was able to create one using a simple query.ow the query I want to use is below but I am getting a syntax error. The old query used one inner join but this one uses two inner join and a left inner join What am I doing wrong? Dim ds As New DataSet() Dim oAdapter As New OleDbDataAdapter Dim sQuery As String = "SELECT c.pkPersonnel," _ [code].....

Help With ADODB Connection Execute Command www.vbcity.com

I have a function that generates SQL statements to run through a connection execute command.Code:         'construct update clause        clause = "Update jobschedule " & vbCrLf        clause = clause & "Set jobschedule.machineid = '" & rsRules!MachineID & "'"        clause = clause & ", optimised = " & OptimisedVal & vbCrLf        clause = clause & "From WOStats as w" & vbCrLf        clause = clause & "Where jobschedule.Status >= 2 And jobschedule.worksorderno = w.worksorderno" & vbCrLf        clause = clause & " and right(jobschedule.bartype,2) " & rsRules!DiameterOp & " " & rsRules!diametervalue & vbCrLf 'diameter        clause = clause & " and jobschedule.shapecode " & StraightOp & vbCrLf ' straight        clause = clause & " and jobschedule.barlength " & rsRules!bmlengthOp & " " & rsRules!bmlengthvalue & vbCrLf ' bar length        clause = clause & " and jobschedule.barweight " & rsRules!bmweightOp & " " & rsRules!bmweightvalue & vbCrLf ' barweight        clause = clause & " and w.WOWeight " & rsRules!OrderWeightOp & " " & rsRules!orderweightvalue & vbCrLf 'order weight        clause = clause & " and w.WObars " & rsRules!OrderBarsOp & " " & rsRules!orderbarsvalue & vbCrLf ' order barsI'm sure the SQL syntax is correct as I've run the same syntax through query analyser. Though I am applying the update to an access database. However if get an error on the "From WOStats as w" line. I need the join in the update statement to cover the final two conditions in the where statement.Can anyone tell me why it's not working?Also is there a restriction to the length of the SQL statement eg 255 characters?Mike

Using Parameters In Sql Querys www.vbforums.com

Hey GuysThanks for reading this post. I'm fairly new to VB that's why i'm not sure how to do this. I just recorded my query and modified to create joins. In my WHERE CLAUSE, I want to make it a parameter so i could just pass a cell and it will get the value. Do you guys know how to do it? I keep getting a syntax error when I run it with a parameter but it runs well when i use a value(i.e. 1111) "SELECT COUNT(*)" _ & Chr(13) & "" & Chr(10) & "FROM QA_PxREZ.dbo.FlightSegment FS " _ & Chr(13) & "" & Chr(10) & "INNER JOIN " _ , _ "QA_PxREZ.dbo.PassengerFlightSegment PFS ON PFS.FlightSegmentID = FS.FlightSegmentID INNER JOIN " _ , _ "QA_PxREZ.dbo.PassengerSpecialNeed PSN ON PSN.PassengerID = PFS.PassengerID INNER JOIN " _ , _ "QA_PxREZ.dbo.SpecialNeed SN ON SN.SpecialNeedID = PSN.SpecialNeedID " _ , _ "WHERE FS.FlightSegmentID = PARAMETER"

One Big Query Or Use Temporary Table? forums.databasejournal.com

Hi, Can any of you tell me what is right? When we are writing a big query that against to 25+ tables (it has lots of lookup and lots of business logic in it so it will have lots of left outer joins, right outer joins, inner join, UNION, group by, and lots of SQL server functions) should we put everything in one select statement? Or we should create lots of temporary tables and then join them together? My friend told me that I should use one statement instead of lots of temp tables in order to reduce the physical read. He does not like the temp table idea since there will be lots of user running the query in the same time and it will use lots of space. But I don�t believe that is true. (I believe when you put everything in one statement, SQL server still needs to create the result set in the temp db.) There will be lots of join going on and it could hurt the performance. I believe that 1.We should create the temp tables and then join the temp tables together later to get the final results. we can create the index on the temp table at runtime. 2.If the order of columns in the index does not match to our where clause, then the index is useless. 3.If there is a SQL function in the where clause, the index is useless. (for example, where Datepart(yy, orderdate)=�1998�) 4.If there is a NOT in the where clause, the index is useless. What do you think? Are you agree with me? Following is the query. Is there any better way to do this? Select d.Description as Division, c.Description as Region, b.StateCode as State, b.FacilityNumber, b.Name as FacilityName, (Case When a.ExitDate Is Not Null Then Convert(Varchar(10),a.ExitDate,101) Else 'Pending' End) as PriorStdSurveyExit, (Case When a.ExitDate Is Not Null And a.IJValid > 0 Then Convert(Varchar(10),a.ExitDate+89,101) Else '' End) as ThreeMonthWindow, (Case When a.ExitDate Is Not Null Then Convert(Varchar(10),a.ExitDate+179,101) When a.IJValid > 0 Then Convert(Varchar(10),a.ExitDate+179,101) Else 'Pending' End) as SixMonthWindow, (Case When a.ExitDate Is Not Null Then Convert(Varchar(10),a.ExitDate+450,101) When a.IJValid > 0 Then Convert(Varchar(10),a.ExitDate+450,101) Else 'Pending' End) as FifteenMonthWindow From ( Select a.FacilityNumber, a.ExitDate, IsNull(b.FacilityNumber,0) as IJValid From ( Select b.FacilityNumber, Max(a.ExitDate) as ExitDate From Survey a Inner Join Facility b on (a.FacilityID = b.FacilityID) Inner Join SurveyFormatCode c on (a.SurveyFormatCodeID = c.SurveyFormatCodeID) Inner Join SurveyTypeCode d on (a.SurveyTypeCodeID = d.SurveyTypeCodeID) Where a.VerbalResultDate is Not Null And c.Description Like 'Standard%' And d.Description Like 'LTC%' And a.ExitDate <= @InDate Group By b.FacilityNumber ) a Left Join ( Select Distinct b.FacilityNumber From Survey a Inner Join Facility b on (a.FacilityID = b.FacilityID) Inner Join SurveyFormatCode c on (a.SurveyFormatCodeID = c.SurveyFormatCodeID) Inner Join SurveyTypeCode d on (a.SurveyTypeCodeID = d.SurveyTypeCodeID) Inner Join ComplianceRatingCode e on (a.ComplianceRatingCodeID = e.ComplianceRatingCodeID) Where a.VerbalResultDate is Not Null And (c.Description Like 'Standard%' And d.Description Like 'LTC%') And e.Description Like 'Category 4%' And a.ExitDate <= @InDate Union Select Distinct b.FacilityNumber From ( Select b.FacilityNumber, Max(a.ExitDate) as ExitDate From SurveyCitation e Inner Join Survey a on (a.SurveyID = e.SurveyID) Inner Join Facility b on (a.FacilityID = b.FacilityID) Inner Join SurveyFormatCode c on (a.SurveyFormatCodeID = c.SurveyFormatCodeID) Inner Join SurveyTypeCode d on (a.SurveyTypeCodeID = d.SurveyTypeCodeID) Inner Join Citation f on (e.CitationID = f.CitationID) Inner Join SSLetterCode g on (f.SSLetterCodeID = g.SSLetterCodeID) Where a.VerbalResultDate is Not Null And d.Description Like 'LTC%' And a.ExitDate <= @InDate And g.SSLetterCode in ('J','K','L') Group By b.FacilityNumber ) a Right Join ( Select b.FacilityNumber, Max(a.ExitDate) as ExitDate From SurveyCitation e Inner Join Survey a on (a.SurveyID = e.SurveyID) Inner Join Facility b on (a.FacilityID = b.FacilityID) Inner Join SurveyFormatCode c on (a.SurveyFormatCodeID = c.SurveyFormatCodeID) Inner Join SurveyTypeCode d on (a.SurveyTypeCodeID = d.SurveyTypeCodeID) Where a.VerbalResultDate is Not Null And c.Description Like 'Standard%' And d.Description Like 'LTC%' And a.ExitDate <= @InDate Group By b.FacilityNumber ) b on (a.FacilityNumber = b.FacilityNumber) Where a.ExitDate >= b.ExitDate ) b on (a.FacilityNumber = b.FacilityNumber) ) a Right Join Facility b on (a.FacilityNumber = b.FacilityNumber) Left Join Region c on (b.RegionID = c.RegionID) Left Join Division d on (c.DivisionID = d.DivisionID) Order By d.Description, c.Description, b.FacilityNumber

Maximum Number Of Tables That Can Be Inner Joined? www.vbcity.com

Hi,using VB6, MS Access, DAO i had posted a question about inner join and i was able to get it to work on a test database and inner joined 3 tables, but when I tried to join my actual database, i had to join 8 tables, i get an error saying "join operation not supported", i was wondering if there is a limitation on how many tables you could inner join, and if there is a limitation what would be a way around it?my sql statement is as follows...Code:sqlstr = "SELECT *, carriers.*, users.*, clarify.*, clarifysub.*, ensemble.*, escalationstatus.*, escalationtype.*, porttypes.*,     smg.* from (((((((( escalation inner join carriers on escalation.spid = carriers.spid)" & _         " inner join status on escalation.statusid = status.statusid)" & _         " inner join clarify on escalation.clarifysid = clarify.clarifysid)" & _         " inner join clarifysub on escalation.clarifysubid = clarify.clarifysubid)" & _         " inner join ensemble on escalation.ensembleid = ensemble.emsemble.id)" & _         " inner join escalationstatus on escalation.statusid = escalationstatus.statusid)" & _         " inner join escalationtype on escalation.etypeid = escalationtype.etypeid)" & _         " inner join porttypes on escalation.porttypeid = porttypes.porttypeid)" & _         " inner join smg on escalation.smgid = smg.smgid where lgno=" & Trim(currentID) & ""I would appreciate all the help.thanxKinara

Limit Joins (or Plain Improve Qry) www.sqlteam.com

Hi, I got a qry that takes 10s. This is off course too long. As I described in my earlier posts, I'm no sql hero, but I'm on a neverending quest to improve. Currently I need a select statement over several (7) tables and return fields from almost all of them. Furthermore I have optional restrictions on fields spread over all those tables, based on a set of parameters. What I initially do is inner join all my tables and then have a big where clause that adds where-rules depending on the arguments given. Like so: select c.clr_id, n.clr_nm, co.long_nm, p.paint_cd, a.appl_desc, m.modl_nm, y.yr_num from CLR c inner join paint_cd p on c.clr_id = p.clr_id inner join clr_use_yr y on y.clr_id = c.clr_id inner join co on co.co_id = c.co_id inner join modl m on y.modl_id = m.modl_id inner join clr_appl ca on c.clr_id = ca.clr_id inner join appl a on ca.appl_cd = a.appl_cd inner join clr_nm n on c.clr_id = n.clr_id WHERE (CASE @regn when '' THEN '' ELSE n.regn_cd END) like (CASE @regn when '' THEN '' ELSE @regn END) AND (CASE @co when '' THEN '' ELSE co.long_nm END) like (CASE @co when '' THEN '' ELSE @co END) AND (CASE @clrNm when '' THEN '' ELSE n.clr_nm END) like (CASE @clrNm when '' THEN '' ELSE @clrNm END) AND (CASE @paintCd when '' THEN '' ELSE p.paint_cd END) like (CASE @paintCd when '' THEN '' ELSE @paintCd END) AND (CASE @applDc when '' THEN '' ELSE a.appl_desc END) like (CASE @applDc when '' THEN '' ELSE @applDc END) AND (CASE @useYear when '' THEN '' ELSE cast(y.yr_num as varchar(4)) end) like (CASE @useYear when '' THEN '' else @useYear END) AND (CASE @modlNm when '' THEN '' ELSE m.modl_Nm END) like (CASE @modlNm when '' THEN '' ELSE @modlNm END) It appeared to me that by getting those where clauses in the join, I would limit the calculations. So the joins where similar to this: inner join paint_cd p on c.clr_id = p.clr_id AND (CASE @paintCd when '' THEN '' ELSE p.paint_cd END) like (CASE @paintCd when '' THEN '' ELSE @paintCd END) The result though, was exactly the same. So, where in my thinking am I wrong and what is the correct way to go about this?

Query Designer Is Messed! www.xtremevbtalk.com

Hi,I'm starting to really hate the Data Environment Query Designer. The following query works with no problems in Access but not it the designer.SELECT DISTINCTROW Service.ServiceName AS `Service/Product`, SUM(Service.ServicePrice) AS `Total`, " Service " AS `Flag`FROM Service INNER JOIN `Session2` ON Service.ServiceID = Session2.ServiceIDWHERE NOT Session2.Free AND NOT Session2.PaidGROUP BY Session2.ServiceID, Service.ServiceNameUNION SELECT DISTINCTROW Product.ProductName AS `Service/Product`, SUM(Product .ProductPrice * Purchase.PurchaseQuantity) AS `Total`, " Purchase " AS `Flag` FROM Product INNER JOIN Purchase ON Product.ProductID = Purchase.ProductID WHERE NOT Purchase.Free AND NOT Purchase.Paid GROUP BY Purchase.ProductID, Product.ProductNameVerifying the statement gives the following errors:Error in SELECT clause: expression near 'DISTINCTROW'Missing FROM clauseError in SELECT clause: expression near '"'Error in WHERE clause near 'Session2'Unable to parse query textAfter I press OK it puts a blank space after Product, so Product.ProductName becomes Product .ProductNameRemoving DISTINCTROW gives this errors and the extra space after Product:Error in SELECT clause: expression near '"'Error in WHERE clause near 'Session2'Error in WHERE clause near 'Purchase'Unable to parse query textThe Query Designer also game me an Unknown error when I had the table name Session (even with [] or ``) so I had to change the name to Session2.Weird stuff, I'm getting annoyedThanks in advance

DataSource Controls :: How To Optimize Query With Multiple Inner Joins forums.asp.net

I have one question regarding how to make the below mentioned type of query more optimized so that mine query perfomace can be increased. Select a.name1,a.address1,b.field3,c.field4,d.field6,e.field7 from a inner join b on a.id=b.id inner join c on c.id=b.id inner join d on d.id=c.id iner join e on e.id=d.id union all Select a1.name1,a1.address1,b1.field3,c.field4,d.field6,e.field7 from a1 inner join b1 on a1.id=b1.id inner join c on c.id=b1.id inner join d on d.id=c.id iner join e on e.id=d.id union all Select a2.name1,a2.address1,b2.field3,c.field4,d.field6,e.field7 from a2 inner join b2 on a2.id=b2.id inner join c on c.id=b2.id inner join d on d.id=c.id iner join e on e.id=d.id All the above three select query at last using the same inner join condition, means last two inner join condition are the same for all the select query. Is there any way so that this query can be optimized?

SQL Inner Join Clause And The Where Clause social.msdn.microsoft.com

Hi everyone, I saw some queries where SQL inner join clause and the where clause is used at the same time. I knew that "on" is used instead of the "where" clause. Would anyone please exaplin me why both "where" and "on" clause is used in some sql Select queries ? Thanks

INTERSECT Clause In JET Database www.vbcity.com

I think Intersect clause is not implemented in Jet 4 Database Engine (MS ACCESS)I have read this clause can be implemented using an Inner JoinBut I would like to implement an Intersect (or an Inner Join) in two subsets of the SAME table. Is it possible?thx

SQL Query Syntax forums.databasejournal.com

I recently wrote a query similar to this: Select e.name, e.employee_id, d.department_name from employees e,departments d where e.department_id = d.department_id An associate recommended that I change this query to add the words INNER JOIN like this: Select e.name, e.employee_id, d.department_name from employees e inner join departments d on e.department_id = d.department_id I was of the understanding that the first query was Microsoft SQL Syntax, and that the second example was ANSI SQL syntax, but that they executed on the server the same way. My associate however thinks that the second example will execute faster. Would anyone like to confirm this?

Correct SQL Syntax www.vbcity.com

strSQL = "SELECT * FROM tblDeal d, Customer c, SoldVehicle sv " & _        "INNER JOIN Customer ON d.custid=c.CustomerID " & _        "AND INNER JOIN d.vehid=sv.VehicleID " & _        "ORDER BY d.id"Help. I can't remember the correct SQL syntax for joining tables. I dunno what I have done wrong. I think you can join three tables ... right?

Page 3 - Multiple Tables Without Relationship forums.devshed.com

Inner join vs outer join: Inner join will remove rows from the first table if there is not a corresponding row in the 2nd table. Outer join will keep the rows, and use all NULL values to fill in the gaps in the 2nd table. In general outer joins are more likely to grow a result set and inner joins are more likely to shrink one (though that will depend on the data and the query). For this reason, it is usually better when building complex queries to try to do your inner joins before your outer joins, because then you will keep your result set smaller for longer and thus the later joins can run faster.

Oracle Query Input Parameter Question social.msdn.microsoft.com

I am using reporting services in VS 2005 to create a report, database is Oracle 9i . I know I can't use MSSQL query syntax to query in my case, so how to write a Oracle query that accept a input parameter AND work at VS 2005 .rdl report? the MSSQL version of the query I want is: Select * from TableA inner join TableB On TableB.id = TableA.id Where TableA.name = @BPARAM1 but because i am connecting with a Oracle database, @BARAM1 will not work. After VS 2005 pop out a parameter dialog and ask for @BARAM1 value, it will give out ORA-00936 error because Oracle use : instand of @. So I change my query to: Oracle version: Select * from TableA inner join TableB On TableB.id = TableA.id Where TableA.name = :BARAM1 Here is the problem, it throw me another error ORA-00936 , I believe it mean :BARAM1 has not assign any value yet? Someone can help me rewrite my query would be most appreciate. Thanks

Pivoting A CTE social.msdn.microsoft.com

� I've done both a CTE and a pivot, but never together.� I did see a few examples out there and followed them, but mine isn't working.� I have four 'tables' within the CTE, and then my final select statement joins all of them and attempts to pivot.� My error is: � Msg 156, Level 15, State 1, Line 90 Incorrect syntax near the keyword 'PIVOT'. � � WITH TwoYrsActual (LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,Week_Idx, LocationType_Code, Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD,"06Actual") AS ( SELECT DISTINCT LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx AS 'Week_Idx', His.LocationType_Code AS 'LocationType_Code', His.Scenario_Idx AS 'Scenario_Idx', Scenario_Code, PlannedSalesAmtUSD, ActualSalesAmtUSD AS '06Actual' FROM dbo.FactInventoryHistory HIS INNER JOIN DimLocation LOC ON His.LocationType_Code = Loc.LocationType_Code INNER JOIN DimDate Date ON His.Week_idx = Date.Date_Idx INNER JOIN DimScenario Scenario ON His.Scenario_Idx = Scenario.Scenario_Idx WHERE Scenario_Code = 'FY06A' GROUP BY LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx, His.LocationType_Code, His.Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD, ActualSalesAmtUSD ), OneYearActual (LocationType_Name, FiscalYear_Name,FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,Week_Idx, LocationType_Code, Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD, "07Actual") AS ( SELECT DISTINCT LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx AS 'Week_Idx', His.LocationType_Code AS 'LocationType_Code', His.Scenario_Idx AS 'Scenario_Idx', Scenario_Code, PlannedSalesAmtUSD, ActualSalesAmtUSD AS '07Actual' FROM dbo.FactInventoryHistory HIS INNER JOIN DimLocation LOC ON His.LocationType_Code = Loc.LocationType_Code INNER JOIN DimDate Date ON His.Week_idx = Date.Date_Idx INNER JOIN DimScenario Scenario ON His.Scenario_Idx = Scenario.Scenario_Idx WHERE Scenario_Code = 'FY07A' GROUP BY LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx, His.LocationType_Code, His.Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD, ActualSalesAmtUSD ), PresentYrActual (LocationType_Name,FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,Week_Idx, LocationType_Code, Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD, "08Actual") AS ( SELECT LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx AS 'Week_Idx', His.LocationType_Code AS 'LocationType_Code', His.Scenario_Idx AS 'Scenario_Idx', Scenario_Code, PlannedSalesAmtUSD, ActualSalesAmtUSD AS '08Actual' FROM dbo.FactInventoryHistory HIS INNER JOIN DimLocation LOC ON His.LocationType_Code = Loc.LocationType_Code INNER JOIN DimDate Date ON His.Week_idx = Date.Date_Idx INNER JOIN DimScenario Scenario ON His.Scenario_Idx = Scenario.Scenario_Idx WHERE Scenario_Code = 'FY08A' GROUP BY LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx, His.LocationType_Code, His.Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD, ActualSalesAmtUSD ), � � PresentYrPlanned (LocationType_Name,FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,Week_Idx, LocationType_Code, Scenario_Idx, Scenario_Code, PlannedTY, ActualSalesAmtUSD) AS ( SELECT LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx AS 'Week_Idx', His.LocationType_Code AS 'LocationType_Code', His.Scenario_Idx AS 'Scenario_Idx', Scenario_Code, PlannedSalesAmtUSD AS PlannedTY, ActualSalesAmtUSD FROM dbo.FactInventoryHistory HIS INNER JOIN DimLocation LOC ON His.LocationType_Code = Loc.LocationType_Code INNER JOIN DimDate Date ON His.Week_idx = Date.Date_Idx INNER JOIN DimScenario Scenario ON His.Scenario_Idx = Scenario.Scenario_Idx WHERE Scenario_Code = 'FY08P' GROUP BY LocationType_Name, FiscalYear_Name, FiscalPeriodOfYear, FiscalWeekOfPeriod, FiscalWeekOfYear,His.Week_Idx, His.LocationType_Code, His.Scenario_Idx, Scenario_Code, PlannedSalesAmtUSD, ActualSalesAmtUSD ) � � select * from ( SELECT PresentYrActual.LocationType_Name, PresentYrActual.FiscalPeriodOfYear, PresentYrActual.FiscalWeekOfPeriod, PresentYrActual.FiscalWeekOfYear, PresentYrActual.LocationType_Code, PresentYrActual.Scenario_Idx, PresentYrActual.Scenario_Code, "08Actual", "07Actual", "06Actual",PlannedTY FROM PresentYrActual FULL OUTER JOIN OneYearActual ON PresentYrActual.FiscalWeekOfYear = OneYearActual.FiscalWeekOfYear AND PresentYrActual.LocationType_Code = OneYearActual.LocationType_Code FULL OUTER JOIN TwoYrsActual ON PresentYrActual.FiscalWeekOfYear = TwoYrsActual.FiscalWeekOfYear AND PresentYrActual.LocationType_Code = TwoYrsActual.LocationType_Code FULL OUTER JOIN PresentYrPlanned ON PresentYrActual.FiscalWeekOfYear = PresentYrPlanned.FiscalWeekOfYear AND PresentYrActual.LocationType_Code = PresentYrPlanned.LocationType_Code ) PIVOT ( SUM(PlannedTY) FOR PresentYrActual.LocationType_Code IN (C, M)) AS pivottable

How To Break Sql Code forums.devshed.com

Hello guys, I have a problem. The sql code you see here is too long, I need to put another where clause in it but i can't type any characters at the end of the code. How can i break this code in more lines?DoCmd.RunSQL "INSERT INTO tblTempSelectie SELECT tblGemeenten.GEM_NAAM, tblLandstreek.LSK_NAAM, tblTellingen.DAG, tblTellingen.MAAND, tblTellingen.JAAR, tblTellingen.HERH_GETAL, tblTellingen.DAG_DEEL_N, tblTellingen.DEEL_TELGE, tblTellingen.YS_NR, tblTellingen.SNEEUW_NR, tblTellingen.S1, tblTellingen.S2, tblTellingen.S3, tblTellingen.S4, tblTellingen.S5, tblTellingen.S6, tblTellingen.S7, tblTellingen.S8, tblTellingen.S9, tblTellingen.S10, tblVogelsoorten.NED_NAAM, tblWaarnemingen.AANTAL FROM tblVogelsoorten INNER JOIN (((tblLandstreek INNER JOIN (tblGemeenten INNER JOIN tblTelgebieden ON tblGemeenten.TGD_GEMNR=tblTelgebieden.TGD_GEMNR) ON tblLandstreek.TGD_LSKNR=tblTelgebieden.TGD_LSKNR) INNER JOIN tblTellingen ON tblTelgebieden.TEL_GEBIED=tblTellingen.TEL_GEBIED) INNER JOIN tblWaarnemingen ON tblTellingen.TEL_NR=tblWaarnemingen.TEL_NR) ON tblVogelsoorten.EUR_NR=tblWaarnemingen.EUR_NR WHERE tblTellingen.MAAND BETWEEN " & a1 & " AND " & b1 & " AND tblTellingen.JAAR BETWEEN " & c1 & " AND " & d1 & " "

DataSource Controls :: Trying To Place A Filter By "MONTH(irActivity.ActivityDate) = 1)" forums.asp.net

I'm trying to count the number of activities in a month without using the Case When clause This is what I was able to produce so far SELECT COUNT(MONTH(irActivity.ActivityDate)) AS 'JAN' FROM irActivity INNER JOIN pdEmployee ON irActivity.Act = pdEmployee.Act ORDER BY 'JAN' I'm trying to place a filter by "MONTH(irActivity.ActivityDate) = 1)" Which then gives me a syntax error

Transact SQL forums.asp.net

I have the following SELECT statement. It returns the first record from EVENT_TEACHERS. I want the last record. 'iID' is the unique identifier so I want to select this stuff where iID is max. How can I add this to my string? I can't get the block out of my head of wanting to put the aggregate in the WHERE clause. str = "SELECT vwStaffList4Assessment.*, EVENT_TEACHERS.iID, EVENT_TEACHERS.iAsmtID, ASMT.* FROM EVENT_TEACHERS INNER JOIN ASMT ON EVENT_TEACHERS.iAsmtID = ASMT.iAsmtID INNER JOIN vwStaffList4Assessment ON ASMT.DVN=vwStaffList4Assessment.DVN" Thanks

C# - Best Way To Add A Column To An Existing Dataset stackoverflow.com

I have two extremely long queries that I am running on multiple tables, using an INNER JOIN. The only difference between these two queries is that they take different input values for one of the WHERE clauses, which they get from two separate DropDownLists. The results of these queries are identical, save for the time1/time2 columns.string query1 = "select Name = t1.name, time1=(SELECT hours FROM table3 WHERE street=list1.selectedvalue), t2.address from [table1] t1 INNER JOIN [table2] t2 ON t1.param = t2.param ORDER BY Name"

What Is Wrong With My Statement? www.sqlteam.com

I actualy using SQL server 2005. I'm a stored procedure and it's accusing error near '=' that is in case clause. Do you know in what I'm wrong? Select u.nick as 'user', l.nick as 'last_user', t.* from threads t inner join users u on t.user_id = u.user_id inner join users l on t.last_user_id = l.user_id where t.creation > @Period and forum_id=(@forum_id) and case when @PageType = 'votacao' then t.votacao = 1 end, case when @PageType = 'topicos' then t.votacao = 0 end, case when @PageType = 'todos' then end Thank you very much in advance.

2 Questions, 1 SQL, 1 Basic www.xtremevbtalk.com

Ok, first off: I'm trying to pull my own query from an Access database. Now the query is already made, and i've tried "SELECT * FROM query_name" but that doesn't work. Doesn't seem to want to let me pull from a query, which is fine. (Unless there is a way to do this. I'd much rather do it this way.)As I tried to work around it I found the SQL syntax from Access and plugged it in to my execute command like so: Set dbConn1 = CreateObject("ADODB.Connection"): dbConn1.Open "MRP", "", "" Set dbrs = dbConn1.Execute("SELECT ServiceOrders.ServiceOrder, ServiceOrders.JobNo, Orders.OrderDate, Customers.ContactFirstName, Customers.ContactLastName, Orders.[Job Address], Orders.Street, ServiceOrders.Occupied, Customers.PhoneNumber " & _ "FROM Purchase Orders INNER JOIN Customers INNER JOIN Orders ON Customers.AccountNumber = Orders.[Account No] INNER JOIN ServiceOrders ON Orders.[Lighthouse Job No] = ServiceOrders.JobNo AND Orders.[Lighthouse Job No] = ServiceOrders.JobNo ON [Purchase Orders].JobNumber = Orders.[Lighthouse Job No] " & _ "WHERE ((([Purchase Orders].Closed)=0));")And that gives me an error on the inner From clause. I originally had parenthesis around the inner join parts, and that generated an error in the Inner Join statements. Here is the SQL directly from Access:SELECT ServiceOrders.ServiceOrder, ServiceOrders.JobNo, Orders.OrderDate, Customers.ContactFirstName, Customers.ContactLastName, Orders.[Job Address], Orders.Street, ServiceOrders.Occupied, Customers.PhoneNumberFROM [Purchase Orders] INNER JOIN ((Customers INNER JOIN Orders ON Customers.AccountNumber = Orders.[Account No]) INNER JOIN ServiceOrders ON (Orders.[Lighthouse Job No] = ServiceOrders.JobNo) AND (Orders.[Lighthouse Job No] = ServiceOrders.JobNo)) ON [Purchase Orders].JobNumber = Orders.[Lighthouse Job No]WHERE ((([Purchase Orders].Closed)=0));Any help would be GREATLY appreciated.Also, I want to display and print reports from the results of these recordsets. What would be the best way to go about this? Recordsets could be as large as 1,000 records or more, but will usually hover around the 200-500 mark.Thank you all for your time.

Joining To Queries In SQL www.vbcity.com

I have two queries as followsqrytempSELECT tblActiveCourse.Cohort, tblStudentCourse.StudentIDFROM tblActiveCourse INNER JOIN tblStudentCourse ON         tblActiveCourse.ActiveCourseID = tblStudentCourse.ActivecourseIDWHERE tblStudentCourse.CurrentStatusID=2QryCompleteSELECT tblStudent.Surname, tblStudent.FirstName, tblStudent.Phone1,             tblStudent.Phone2, tblPreferedDays.PreferedDays,             tblStudentCourse.DateAdded, tblStudentCourse.IncludeDate,             tblStudentCourse.Note, tblStudentCourse.StudentCourseID,             tblStudentCourse.StudentID, tblStudentCourse.ActivecourseID,             tblStudentCourse.CurrentStatusID, qrytemp.CohortFROM qrytemp          RIGHT JOIN (tblPreferedDays          RIGHT JOIN ((tblCourse INNER JOIN tblStudentCourse ON tblCourse.CourseID =                             tblStudentCourse.CourseID)          INNER JOIN tblStudent ON tblStudentCourse.StudentID = tblStudent.StudentID)                            ON tblPreferedDays.PreferedDaysID =                             tblStudentCourse.PreferedDaysID) ON qrytemp.StudentID =                             tblStudentCourse.StudentIDWHERE (((tblStudentCourse.CurrentStatusID)=1) AND             ((tblStudentCourse.CourseID)=29))ORDER BY tblStudentCourse.DateAdded;What I want to do is have it all as one big SQL Statement. So instead of having a query joining a query I want to have some sort of join in SQL.The reason I had to do it like this is because i needed a WHERE clause in my qrytemp so that I didnt get duplicate rows and I didnt know how to implement this.I thought maybe I could do the first query like(SELECT *FROM ... WHERE ....) as tblTemp and then join those selected rows in SQL using tblTemp instead of qrytemp which is effectivley what joining to queries does.

Self Join With Multiple Tables www.xtremevbtalk.com

Hi, Ive a working Query...SELECT dbo.TblLookupPreferences.ContactType, dbo.TblEmailAddress.Email, dbo.TblEmailAddress.Name, dbo.TblEmailAddress.Title, dbo.TblEmailAddress.DoNotMail, dbo.TblLookupPreferences.AssociatedTypeFROM dbo.TblAdmMain INNER JOIN dbo.TblLookupPreferences ON dbo.TblAdmMain.AdminID = dbo.TblLookupPreferences.AssociatedID INNER JOIN dbo.TblEmailAddress ON dbo.TblLookupPreferences.LookupValue = dbo.TblEmailAddress.EmailIDWHERE (dbo.TblLookupPreferences.ContactType = '7') AND (dbo.TblEmailAddress.DoNotMail = 'N') AND (dbo.TblLookupPreferences.AssociatedType = 'A')But Now I want to add another table to the same view..The Other TableName is 'TblProspectmembers'In need to join tblprospectmembers [Memno] field = TblLookupPreferences.AssociatedIDWHERE TblLookupPreferences.AssociatedType = 'M'but I cant figure out the exact syntax for this.any help would be highly appreciated!(oh im using Sql server 2000 and Access 2000 as a front end)

Inner Join To Outer Join Problem www.sqlteam.com

hello, i am running mysql server 5 and i have sql syntax like this: select sales.customerid as cid, name, count(saleid) from sales inner join customers on customers.customerid=sales.customerid group by sales.customerid order by sales.customerid; it works fine and speedy. but when i change inner join to right join, in order to get all customers even there is no sale, my server locks up. note: there is about 10000 customers and 15000 sales. what can be the problem? thanks,

Help: Converting Complex T-SQL JOIN To ANSI JOIN Style forums.databasejournal.com

Hi, if you consider yourself a SQL guru and want a challenge you might consider answearing this question: I'm converting some old T-SQL Syntax to fully ANSI compatible SQL syntax. I've converted some JOIN operations to ANSI JOIN without any problem until I ran into this: SELECT c.fldSettlementDate, d.Account, d.IBPortfolioID, d.IBCurrency, b.fldIBAmount, 0, a.fldLocalCurrencyAmount), 0, 0 FROM tblFlowTransaction a, tblInitialBalance b, tblBankDays c, tblTempTrans d WHERE c.fldSettlementDate= @datecounter AND c.fldSettlementDate *=a.fldSettlementDate ANDd.tcount= @accountcounter ANDd.Account= b.fldAccount ANDb.fldAccount *=a.fldAccount ANDd.IBCurrency= b.fldCurrency AND b.fldCurrency*=a.fldCurrency AND d.IBPortfolioID= b.fldPortfolioID ANDb.fldPortfolioID*=a.fldPortfolioID I've tried a lot of ways of converting this JOIN to ANSI JOIN but none of them returns the same result as the original T-SQL JOIN. My last try that didn't work is: SELECT c.fldSettlementDate, d.Account, d.IBPortfolioID, d.IBCurrency, b.fldIBAmount, 0, a.fldLocalCurrencyAmount, 0, 0 FROM tblTempTrans d INNER JOIN tblInitialBalance b ONd.Account= tblInitialBalance.fldAccount ANDd.IBCurrency= tblInitialBalance.fldCurrency AND d.IBPortfolioID= tblInitialBalance.fldPortfolioID AND d.tcount= @accountcounter, LEFT OUTER JOIN tblFlowTransaction a ONb.fldAccount = a.fldAccount AND b.fldCurrency= a.fldCurrency ANDb.fldPortfolioID= a.fldPortfolioID, RIGHT OUTER JOIN tblBankDays c ONa.fldSettlementDate= c.fldSettlementDate AND c.fldSettlementDate= @datecounter Does anyone have experience of converting these kinds of JOINS? How to approach the problem? Is there a "algorithm" you can use when you make the conversion or is it just "trial and error"? Grateful for any help & hints! /Henrik

Complex Search With Multiple Inner Joins - Version: 2000 (9.0) www.utteraccess.com

I'm having an issue with a search I'm writing and I am hoping someone can help. Here is the background: I have search form (frmSearch) that is used to set criteria for the filtering of another form (frmMainForm). The main form has several subforms, and in order to be able to search on both Main Form and Sub Form Criteria, the code behind frmSearch builds strings for both frmMainForm.RecordSource and frmMainform.Filter. The code sets the recordsource based on subform criteria and then the filter based on main form criteria. The sql to build the recordsource is: "SELECT DISTINCT measures.* " & _ "FROM ((((measures " & _ "INNER JOIN qryPendingStatusB ON (measures.measure = qryPendingStatusB.measure) AND (measures.owners_set = qryPendingStatusB.owners_set))" & _ "INNER JOIN qryRetiredStatusB ON (measures.owners_set = qryRetiredStatusB.owners_set) AND (measures.measure = qryRetiredStatusB.measure))" & _ "INNER JOIN qryFirstRetired ON (measures.owners_set = qryFirstRetired.owners_set) AND (measures.measure = qryFirstRetired.measure))" & _ "INNER JOIN qryFirstReported ON (measures.measure = qryFirstReported.measure) AND (measures.owners_set = qryFirstReported.owners_set))" & _ "INNER JOIN measureset_junction ON (measures.measure = measureset_junction.measure) AND (measures.owners_set = measureset_junction.owners_set)" & _ "WHERE " The WHERE part then gets built based on search form controls. This whole scheme worked fine when I was first starting to build it and had just one INNER JOIN. But now that I have several, when I run the search, I get messages telling me that criteria on the main form that are in the frmMainForm.Filter string could be in any of the tables that I inner join. For example, if the filter is (measures.condition = 'AMI") I get: "The specified field 'measures.condition' could refer to more than one table listed in the FROM clause of your SQL statement" I don't understand this, since when I say 'measures.condition' I thought I am clearly telling it to look for the field 'condition' in the table 'measures.' Does anyone have any ideas for how I can be more explicit in terms of where this the filter should look. Does this have anything to do with the way I wrote my inner joins? I tried the following (note the lack of parenthesis in front of measures and the corresponding closing parenthesis), but I just got a syntax error when I tried to compile: "SELECT DISTINCT measures.* " & _ "FROM measures " & _ "INNER JOIN qryPendingStatusB ON (measures.measure = qryPendingStatusB.measure) AND (measures.owners_set = qryPendingStatusB.owners_set)" & _ "INNER JOIN qryRetiredStatusB ON (measures.owners_set = qryRetiredStatusB.owners_set) AND (measures.measure = qryRetiredStatusB.measure)" & _ "INNER JOIN qryFirstRetired ON (measures.owners_set = qryFirstRetired.owners_set) AND (measures.measure = qryFirstRetired.measure)" & _ "INNER JOIN qryFirstReported ON (measures.measure = qryFirstReported.measure) AND (measures.owners_set = qryFirstReported.owners_set)" & _ "INNER JOIN measureset_junction ON (measures.measure = measureset_junction.measure) AND (measures.owners_set = measureset_junction.owners_set)" & _ "WHERE " Thanks Much!

SQL Server 2005 Non-ansi Joins: Any Easy Solutions? social.msdn.microsoft.com

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? Thanks, AIMDBA

Joins And Returning Rows www.sqlteam.com

Using MS SQLServer 2000 Is there a way to create a query that will return only 1 row within a join for example: select a,b,c,d from tbl1 inner join tbl2 on top 1 tbl2.a = tbl1.a <-- return only the top record here... I dont have too much of a problem with joins... however I was wondering if there was a mechanism to just specify what would be the top most record from a join clause. thanks Kevin

Stored Proc Question! Toughy !!! www.vbforums.com

I want to dynamically load the last part of my WHERE clause by placing the variable @Wcl in place of the AND (dbo.Sections.SectionID = 13) AND (dbo.CJQDetail.ResponseID >= 76 AND dbo.CJQDetail.ResponseID <= 78)WHERE clause like so: Can it be done?Code:declare @WCl varchar(100)set @Wcl = 'AND (dbo.CJQDetail.ResponseID >= 76 AND dbo.CJQDetail.ResponseID <= 78)'SELECT COUNT(dbo.Response.QuestionID) AS ResponsesFound, dbo.Response.QuestionID, dbo.CJQDetail.CJQID, dbo.Questions.SectionID, dbo.Sections.SectionName, dbo.Questions.MinResponse, dbo.Sections.FormID, dbo.Sections.AspPageNum, dbo.Questions.QuestionID AS Expr1, dbo.CJQDetail.ResponseIDFROM dbo.CJQDetail INNER JOIN dbo.Response ON dbo.CJQDetail.ResponseID = dbo.Response.ResponseID AND dbo.CJQDetail.ResponseID = dbo.Response.ResponseID INNER JOIN dbo.Questions ON dbo.Response.QuestionID = dbo.Questions.QuestionID INNER JOIN dbo.Sections ON dbo.Questions.SectionID = dbo.Sections.SectionIDGROUP BY dbo.Response.QuestionID, dbo.CJQDetail.CJQID, dbo.Questions.SectionID, dbo.Sections.SectionName, dbo.Questions.MinResponse, dbo.Sections.FormID, dbo.Sections.AspPageNum, dbo.Sections.SectionID, dbo.Questions.QuestionID, dbo.CJQDetail.ResponseIDHAVING (dbo.CJQDetail.CJQID = 5) AND (dbo.Sections.FormID = 1) AND (dbo.Sections.SectionID = 13) AND (dbo.CJQDetail.ResponseID >= 76 AND dbo.CJQDetail.ResponseID <= 78)

Combining DISTINCT, TOP And ORDER BY?? www.xtremevbtalk.com

OK. How do I go about getting the TOP 10 (most recent) entries from a database table while using the DISTINCT sql command with it? I'm querying 4 tables using inner joins. From the tblJob table, all I want is the last 10 entries which have a matching custID and I don't want duplicates on the destID fields. Here's what I have so far: Code:SELECT Top 10 DISTINCT tblJob.destID, tblJob.custID, tblDestination.destID, tblDestination.destAddr1, tblDestination.destAddr2, tblDestination.cityID, tblDestination.zoneID, tblZone.zoneID, tblZone.zoneName, tblCity.cityID, tblCity.cityName, tblCity.stateID, tblJob.jobStatusFROM ((tblJob INNER JOIN tblDestination ON tblJob.destID = tblDestination.destID) INNER JOIN tblCity ON tblCity.cityID = tblDestination.cityID) INNER JOIN tblZone ON tblDestination.zoneID = tblZone.zoneIDWHERE tblJob.custID = 2 AND tblCity.StateID = 11 AND tblJob.jobStatus = 'Completed'ORDER BY tblJob.jobID DESCIf I remove the TOP 10 AND the ORDER BY clause then this query works (even though there are only 2 matching records in the database table right now). But with the ORDER BY clause in there, I get this error:Quote:ORDER BY clause (tblJob.jobID) conflicts with DistinctIf I remove the ORDER BY clause but put the TOP 10 back in, then I get this error:Quote:Syntax error (missing operator) in query expression 'Distinct tblJob.destID'I can't understand why a Distinct on the tblJob.destID field would cause a sorting problem with the tblJob field?And is there anyway to combine TOP, DISTINCT, & ORDER BY in one SQL statement?Oh, by the way, I'm running VB 6.0 and Access 2000.Thank you in advance for any help that you can give.DTFan

SQL Query, Inner Join 3 Tables www.vbforums.com

Gooday all,got the followin query, to bring data from 2 related tables.VB Code:sSQL = "SELECT P.*, PD.Dtext FROM Products AS P INNER JOIN PaDimensions AS PD ON P.PaDim = PD.Id WHERE ProductName= '" & Combo5.Text & "'" cant i seem to get the syntax right to get data from 3 related tables. tried many varietions like :VB Code:sSQL = "SELECT P.*, PD.Dtext, PP.Ptext FROM Products AS P INNER JOIN PaDimensions AS PD ON P.PaDim = PD.Id  [color=Red]AS P INNER JOIN Price AS PP ON P.PriceCode = PP.Id[/color] WHERE ProductName= '" & Combo5.Text & "'" orVB Code:sSQL = "SELECT P.*, PD.Dtext, PP.Ptext FROM Products AS P INNER JOIN PaDimensions AS PD ON P.PaDim = PD.Id [color=Red]AND P INNER JOIN Price AS PP ON P.PriceCode = PP.Id[/color] WHERE ProductName= '" & Combo5.Text & "'" no success. when :"Price"= 3rd related table."Id" is a field in "Price" related, on to many, to "PriceCode" field in "Products"(in red are my additions to original query)thanks in advanse for ideas/suggestions..regards,

Sub Query Error(its Very Urgent) forums.asp.net

 hi frenz: This is my query:it shows the following error:Incorrect syntax near the keyword 'as'.plz let me know my error, and how to correct it. My Query: SELECT EH.EmpID, EL.UserName,EL.Password, E.FirstName, E.LastName, E.DesignationID, E.DepartmentID, E.Sex, E.DOB, E.Age, E.Address, E.Email, 'ContactNo'= Case When E.phone is not null Then E.phone When E.phone is null Then E.Mobile End ,E.DateOfjoining, D.Designation, DE.Department,E.HobbyID where E.HobbyID in (select EH.HobbyID from EMP_Tbl_EmployeeHobbies EH inner join EMP_Tbl_Hobby H on EH.HobbyID=H.HobbyID) as item1, From dbo.EMP_Tbl_Employee E Inner Join dbo.EMP_Tbl_Designation D On E.DesignationID=D.DesignationID Inner Join dbo.EMP_Tbl_Department DE On E.DepartmentID=DE.DepartmentID Inner Join dbo.EMP_Tbl_EmployeeHobbies EH On E.EmpID=EH.EmpID Inner Join dbo.EMP_Tbl_EmployeeLogin EL On E.Sno=EL.Sno

Dsn-Less Connection www.xtremevbtalk.com

Hi ,Could you kindly try to help me out with the following coding difficulty?When doing an INNER JOIN query using a DSN connection (i.e., "DSN=Signals") I get ALL the requested fields in my recordset. However, when doing the same INNER JOIN query using a DSN-Less Connection (i.e., "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:S&IDBSignals.mdb") there are TWO fields missing data in my recordset.Why would this be? Is there a different query syntax required when using DSN-Less connections? Am I doing the DSN-Less Connection right?The code in question is the following:------------------------------------Set c = New ADODB.Connectionc.Open (DSN or DSN-Less code as listed above)Set rsReportTest = New ADODB.RecordsetsSQL = "SELECT TSignals.*,TMaintenance.*,TPartsReplaced.*" _& "FROM (TSignals INNER JOIN TMaintenance ON TSignals.SId = TMaintenance.SId)" _& "INNER JOIN TPartsReplaced ON TMaintenance.ID = TPartsReplaced.RID"rsReportTest.Open sSQL, c, adOpenStatic, adLockOptimistic---------------------The fields missing data are "ID" and "SID", which happen to be the key (INNER JOIN) fields.Thank you for your attention.Salaroche

Datareport Urgent Help www.vbforums.com

HiI want to generate a report Based on the following querySQL = "SELECT * from test inner Join Client on test.ClientID=Client.ClientID"SQL = SQL & " inner Join Event on test.EventID=Event.EventID inner Join Duration on test.IntID=Int.DurationID"SQL = SQL & " INNER JOIN Room on test.RoomID=Satellite.RoomID inner join Guest on test.GuestID=Guest.GuestID where BID=4"I will be using the Data report present in VB6(my database is Access)in My Report I want to display the Following Fields:ClientEventDurationRoomGuest...How can I do it?thanks

Query Returning 3 Repeated Records Per ID? www.access-programmers.co.uk

I am trying to build a query which will find the costs from a set of tables where the client has costs. The client has a cost on booking a fee, he has a hotel room cost and a room facility cost. My query brings up what I want but each result is repeated 3 times. I don't know why, can someone correct me please. SELECT DISTINCT Addresses.ID, Holiday_Bookings.Initial_Cost, Room_Facilities.Cost, Rooms.BasicCostPerNight FROM ((Room_Facilities INNER JOIN ((Hotels INNER JOIN Rooms ON Hotels.HotelID = Rooms.HotelID) INNER JOIN Stops ON Hotels.HotelID = Stops.HotelID) ON Room_Facilities.FacilityID = Rooms.FacilityID) INNER JOIN (Routes INNER JOIN WalkTypes ON Routes.RouteID = WalkTypes.RouteID) ON Stops.StopID = Routes.Arrive_In) INNER JOIN (Holiday_Bookings INNER JOIN (Groups INNER JOIN (Clients INNER JOIN Addresses ON Clients.ID = Addresses.ID) ON Groups.ID = Clients.GroupID) ON Holiday_Bookings.ID = Clients.ClientID) ON WalkTypes.Walk_TypeID = Groups.GroupID;

Syntax For Order By Clause In Linq To Sql That Has Select New With stackoverflow.com

I have a LINQ to sql statement that joins 2 tables. I would like to add a order by clause on one of the columns. However the order by clause does not seem to take effect at all. right syntax in VB.net to achieve order by in the following: Dim query = From dtIt In dbsomecontext.mytable Join dtIl In dbsomecontext.anothertable On dtIt.ItemID Equals dtIl.ItemID Where dtIl.IsAvailable = True Order By dtIt.manufacturer [Code]...

SQL Server :: Combine Inner Joins - Get A Field Value From The First Statement forums.asp.net

I have a select statement. I need to get a field value from the first statement. GOAL: Below is what I am trying to accomplish - the code below is not correct - Please modify the statement with the correct SQL Syntax. I want to query a table and based on the results I want to set the "If THE Else"statement to query then table with diffent inner joins. SELECT * FROM CONTENTITEM CI INNER JOIN CONTENTCATEGORY CC ON CI.CATEGORYID = CC.CATEGORYID IF CI.FORMTYPE = 200 OR 300 SELECT * FROM CONTENTITEM CI INNER JOIN CONTENTCATEGORY CC ON CI.CATEGORYID = CC.CATEGORYID INNER JOIN CONTENTFORMFIELD CF ON CF.ITEMID = CI.ITEMID WHERE CI.ITEMID = @ITEMID ELSE SELECT * FROM CONTENTITEM CI INNER JOIN CONTENTCATEGORY CC ON CI.CATEGORYID = CC.CATEGORYID WHERE CI.ITEMID = @ITEMID

ADO.NET :: LINQ To SQL - How Do You Join More Than 2 Tables forums.asp.net

I was trying to find out how I can join more than 2 tables using the LINQ-to-SQL syntax. For instance, joining 2 tables in SQL: SELECT * FROM Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.Key=T2.Column1WHERE T2.Key='17'; can be expressed as: var Result = from T1 in DbContext.Table1 join T2 in DbContext.Table2 on T1.Key equals T2.ForeignKey where T2.Key=17 select new { T1, T2 }; But how would I join 3 or more tables using LINQ? For example: SELECT * FROM (Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.Key=T2.Column1) INNER JOIN Table3 AS T3 ON T3.Key=T2.Column2 WHERE T2.Key='37'; I've been searching and experimenting and I cannot seem to find any informraiton on this. One example I found involves putting the result for the frist join into a temp object, and then performing the second join. I'm not sure performance-wise if that's the same as doing a 3-table join directly using a single SQL statement.

Differ Bet WHERE Clause && INNER JOIN? www.dbforums.com

In simple terms, if possible, what is the difference between using the WHERE clause in a SELECT statement vs an INNER JOIN? According to Rob Viera's book the WHERE is "inclusive" in nature, meaning that all records that meet the WHERE condition are included in the result set. The text further stated that an INNER JOIN is "exclusive" in nature meaning that data not meeting the JOIN condition is excluded from the result set. In layman's terms, what is the difference? Any examples? Thanks in advance. ddave

Need Help With My SQL Statement forums.asp.net

I am trying to figure out what is wrong with my sql statement.  It shows up right and all but my last and where clause is not working right.  I want to search the database for a parameter of searchtext but only display jobs where the expiration date is greater than todays date.  My statement is as follows:  SELECT tblJobs.intJobID, tblJobs.vcBusinessName, tblJobs.vcJobTitle, tblJobs.vcJobShortDescription, tblJobs.vcJobExpirationDate, tblJobs.vcJobDescription, tblJobs.IntJobCategory, tblJobs.intJobSchedule, tblJobs.vcPositionExperience, tblJobs.vcJobInquiry, tblJobs.vcApplicationPath, tblJobs.vcFileName, tblJobs.intApplicationSize, tblSalary.vcSalary, tblCity.vcCityName, tblJobCategory.vcJobCategory, tblPositionSchedule.vcJobScheduleType FROM tblJobs INNER JOIN tblSalary ON tblJobs.intSalaryID = tblSalary.intSalaryID INNER JOIN tblPositionSchedule ON tblJobs.intJobSchedule = tblPositionSchedule.intJobScheduleTypeID INNER JOIN tblJobCategory ON tblJobs.intJobCategory = tblJobCategory.intJobCategoryId INNER JOIN tblCity ON tblJobs.intCityID = tblCity.intCityId WHERE tblJobs.vcJobTitle LIKE '%' + @SearchText + '%' OR tblJobs.vcBusinessName LIKE '%' + @SearchText + '%' OR tblJobs.vcJobShortDescription LIKE '%' + @SearchText + '%' OR tblJobs.vcJobDescription LIKE '%' + @SearchText + '%' OR tblJobs.vcPositionExperience LIKE '%' + @SearchText + '%' OR tblJobs.vcJobInquiry LIKE '%' + @SearchText + '%' And tblJobs.vcJobExpirationDate > getdate() AND tblJobs.intJobCategory = @CID AND tblJobs.IntJobSchedule = @SID ORDER BY intjobid DESC END 

SqlDataSource forums.asp.net

Hey all, I've just started to use asp.net and so far have found it fairly easy. The only problem I've come across is with putting variable clauses into the SqlDataSources. This is one example of what I've done trying to get this to work.  <asp:SqlDataSource ID="ClaimsDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" SelectCommand="SELECT TOP 20 dbo_ClaimStatus.ClaimStatus, dbo_CLAIMS.PACKET, dbo_INSURED.NAME, dbo_FEES.INSURER, dbo_FEES.CLAIM_NUMBER, dbo_CLAIMS.LOSS_DATE, dbo_CLAIMS.RECEIVED, dbo_CLAIMS.LOSS_LOCATION, dbo_CLAIM_TYPES.CODE FROM ( ( ( ( dbo_CLAIMS INNER JOIN dbo_CLAIM_TYPES ON dbo_CLAIMS.CLAIM_TYPE = dbo_CLAIM_TYPES.CODE ) INNER JOIN dbo_ClaimStatus ON dbo_CLAIMS.StatusID = dbo_ClaimStatus.ClaimStatusID ) INNER JOIN dbo_FEES ON dbo_CLAIMS.PACKET = dbo_FEES.PACKET ) INNER JOIN dbo_INSURED ON dbo_CLAIMS.PACKET = dbo_INSURED.PACKET ) WHERE dbo_ClaimStatus.ClaimStatus = @claimStatus"> <FilterParameters> <asp:QueryStringParameter Name="claimStatus" QueryStringField="ctl00$MainContent$claimStatus" /> </FilterParameters> </asp:SqlDataSource> I'm posting claimStatus from a previous page using PostBackUrl on an <asp:Button /> tag and claimStatus is an <asp:DropDownList /> tag. Any idea's as to why this doesn't work? Nalum 

SQL-92 Outer Join Vs T-SQL Outer Join (6.5 Or 7.0) - Test Script Included forums.databasejournal.com

Take the following scenario: We have two tables that have somewhat of a parent-child relationship. We are trying to use a SQL-92 outer join that returns the same results as a TSQL *= outer join. The difficulty we are having is that some of the parent records do not have any corresponding child records, but we still want to see those parent records with 0 (zero) for the count. How can we accomplish this with a SQL-92 compliant join (if it is even possible)? In the query results below, we would like the first set of results. Thanks in advance for any help. -David Edelman Test script below, followed by results =========================================== create table parent (p_id int NOT NULL) go create table child (p_id int NOT NULL, c_type varchar(6) NULL) go insert parent values (1) insert parent values (2) insert parent values (3) insert parent values (4) insert parent values (5) insert parent values (6) insert parent values (7) insert parent values (8) insert parent values (9) insert parent values (10) go insert child values (1, 'AAA') insert child values (1, 'BBB') insert child values (1, 'CCC') insert child values (2, 'AAA') insert child values (4, 'AAA') insert child values (4, 'DEF') insert child values (4, 'AAA') insert child values (4, 'BBB') insert child values (5, 'AAA') insert child values (5, 'AAA') insert child values (6, 'AAA') insert child values (7, 'AAA') insert child values (7, 'BBB') insert child values (7, 'CCC') insert child values (7, 'DDD') insert child values (10, 'AAA') insert child values (10, 'CCC') go select p.p_id, count(c.p_id) as num_rows from parent p, child c where p.p_id *= c.p_id and c.c_type in ('AAA', 'BBB') group by p.p_id select p.p_id, count(c.p_id) as num_rows from parent p left outer join child c on p.p_id = c.p_id where c.c_type in ('AAA', 'BBB') group by p.p_id ========================================= Results: (T-SQL *= outer join) p_id num_rows ----------- ----------- 1 2 2 1 3 0 4 3 5 2 6 1 7 2 8 0 9 0 10 1 (SQL-92 outer join) Warning: Null value eliminated from aggregate. p_id num_rows ----------- ----------- 1 2 2 1 4 3 5 2 6 1 7 2 10 1

SQL-92 Outer Join Vs T-SQL Outer Join (6.5 Or 7.0) - Test Script Included forums.databasejournal.com

cross-posted from Discussion about SQL Server forum http://www.swynk.com/discuss_sql/getmessage.asp?rootid=105244&id=105244

MS-SQL Server Equivalent To Oracle 9i? bytes.com

All, Oracle 9i provides a "USING" clause option for inner joins, that allows me to say: SELECT * FROM TBL1 JOIN TBL2 USING KeyColumn assuming KeyColumn is in both TBL1 and TBL2. This is HIGHLY desirable for our software make use of, but we also support SQL Server. There is no USING option available, and SELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumn causes an ambiguous column error on KeyColumn. Is there any equivalent to this Oracle functionality on SQL Server? KingGreg

Automate Variable Input To Query? www.sitepoint.com

I have query that works as follows: Code: db.query("""SELECT traveler.travelerFirstName,vaccine.vaccineName from (traveler INNER JOIN takenvaccine ON traveler.travelerID = takenvaccine.travelerID) INNER JOIN vaccine ON takenvaccine.vaccineID=vaccine.vaccineID INNER JOIN requiredvaccine ON vaccine.vaccineID=requiredvaccine.requiredvaccineID INNER JOIN city ON requiredvaccine.cityID = city.cityID WHERE traveler.travelerFirstName = 'John'""") The output gives me all vaccines taken by a given employee. To allow the user to choose different names when running the system, I am trying to use a variable, named person: Code: person = "John" db.query("""SELECT traveler.travelerFirstName,vaccine.vaccineName from (traveler INNER JOIN takenvaccine ON traveler.travelerID = takenvaccine.travelerID) INNER JOIN vaccine ON takenvaccine.vaccineID=vaccine.vaccineID INNER JOIN requiredvaccine ON vaccine.vaccineID=requiredvaccine.requiredvaccineID INNER JOIN city ON requiredvaccine.cityID = city.cityID WHERE traveler.travelerFirstName = 'person'""") Then I run the query inside my python program. The first version without variable works fine. But the second, using variable, doesn't give me any output. What I am missing here about the sintax to make the MySQL work with variable ...

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &&<, &&<=... social.msdn.microsoft.com

I am getting an error as Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. �while running the following query. � SELECT DISTINCT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName, LUP_FIX_DeptDetails.DeptName AS CurrentDepartment, LUP_FIX_DesigDetails.DesigName AS CurrentDesignation, LUP_FIX_ProjectDetails.ProjectName AS CurrentProject, ManagerName=(SELECT E.FirstName+' '+E.LastName FROM EmployeeDetails E INNER JOIN LUP_EmpProject ON E.Empid=LUP_EmpProject.Empid INNER JOIN LUP_FIX_ProjectDetails ON LUP_EmpProject.Projectid = LUP_FIX_ProjectDetails.Projectid WHERE LUP_FIX_ProjectDetails.Managerid = E.Empid) FROM EmployeeDetails INNER JOIN LUP_EmpDepartment ON EmployeeDetails.Empid=LUP_EmpDepartment.Empid INNER JOIN LUP_FIX_DeptDetails ON LUP_EmpDepartment.Deptid=LUP_FIX_DeptDetails.Deptid AND LUP_EmpDepartment.Date=(SELECT TOP 1 LUP_EmpDepartment.Date FROM LUP_EmpDepartment WHERE EmployeeDetails.Empid=LUP_EmpDepartment.Empid ORDER BY LUP_EmpDepartment.Date DESC) INNER JOIN LUP_EmpDesignation ON EmployeeDetails.Empid=LUP_EmpDesignation.Empid INNER JOIN LUP_FIX_DesigDetails ON LUP_EmpDesignation.Desigid=LUP_FIX_DesigDetails.Desigid AND LUP_EmpDesignation.Date=(SELECT TOP 1 LUP_EmpDesignation.Date FROM LUP_EmpDesignation WHERE EmployeeDetails.Empid=LUP_EmpDesignation.Empid ORDER BY LUP_EmpDesignation.Date DESC) INNER JOIN LUP_EmpProject ON EmployeeDetails.Empid=LUP_EmpProject.Empid AND LUP_EmpProject.StartDate=(SELECT TOP 1 LUP_EmpProject.StartDate FROM LUP_EmpProject WHERE EmployeeDetails.Empid=LUP_EmpProject.Empid ORDER BY LUP_EmpProject.StartDate DESC) INNER JOIN LUP_FIX_ProjectDetails ON LUP_EmpProject.Projectid=LUP_FIX_ProjectDetails.Projectid WHERE EmployeeDetails.Empid=1 � PLEASE HELP.................

SQL Syntax?!&gt; www.xtremevbtalk.com

Is there anything wrong w/ the syntax below? When using an Access DB it works fine, but with MSDE i get the following error:Run-time error '-2147217900...."Incorrect syntax near the Keyword 'INNER'. Code:MatCN.Execute "UPDATE MatTran INNER JOIN MatOnHand ON " & _ MatTran.MatNo = MatOnHand.MatNo SET MatTran.BillPO = '" & _ PONum & "', MatTran.BillDate = '" & Date & "', MatTran.Billed = 1 " & _ WHERE MatOnHand.Vendor='" & vendor & "'"

Access Database And Vbscript. Please Help Me Out. www.vbcity.com

Hello,Maybe not the right newsgroup for posting vbscript questions, but...I made an asp page and i'm trying to do a query on an ms-access database. The query looks as follows:Code:<%  Set DB = Connect()      SQL = "SELECT T2.GEBRUIKER, T3.TITEL FROM BEHEER AS T1 "      SQL = SQL & "INNER JOIN GEBRUIKERS AS T2 ON T1.GEBRUIKER = T2.GEBRUIKER "      SQL = SQL & "INNER JOIN SOFTWARETITELS AS T3 ON T1.SOFTWARETITEL = T3.TITEL "  Set RS = DB.Execute (SQL)%>So it's just a simple query with joins. When i'm trying to run this, it shows the following error displayed below. When i cut the last portion with the inner join on the softwaretitles it works just fine. I also imported the database into SQL-server, when trying to run the same query on the SQL-server query analyzer its just running fine.Error Type:Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'T1.GEBRUIKER = T2.GEBRUIKER INNER JOIN SOFTWARETITELS AS T3 ON T1.SOFTWARETITEL = T3.TITEL'./LicentieBeheer/Menu.asp, line 15Thanks for your spare time! 

Inner Join 3 Tables www.xtremevbtalk.com

i always get a syntax error on the following statement:sqltekst = "select * from TblGebruikers inner join Tblstudenten on TblGebruikers.Gebruikersnr = Tblstudenten.Gebruikersnr and inner join TblLesgevers on TblGebruikers.Gebruikersnr = TblLesgevers.Gebruikersnr order by TblGebruikers.Naam"tnx in advance for the helpDes

DataSource Controls :: Incorrect Syntax Near Key Word Inner... forums.asp.net

I hv Writte nA sql Query in whic hi want to use an Inner join as well as THe left OUter Join in it ..i hv written it in the sql server it executes properly.but when i run the same code ..from my application .it starts gving the Error Incorrect Syntax Near Keyword INNER.. [code]....

UPDATE Statement Involving Tables In Different Databases bytes.com

I want to create an UPDATE statement that updates a value in a table in database A based on the results of an inner join between tables each residing in deifferent databases. What is the correct syntax for doing this? The following should give you an idea of what I'm trying to do: UPDATE A.dbo.tblCars SET A.dbo.tblCars.Car = 'Ferrari' FROMA.dbo.tblCars INNER JOIN B.dbo.tblHouses ON A.dbo.tblCars.RecID = B.dbo.tblHouses.RecID WHERE (B.dbo.tblHouses = 'Mansion')

Using IF With JOINs www.dbforums.com

SQL Novice 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'

Can We Have An Inner Join Clause In An Update Statement forums.databasejournal.com

Hi, I'm trying to inner join an update statement. Something like this: update #point_connection_temp AS a inner join #point_connection_temp_two as b on a.key_fld = b.key_fld set a.next_point = b.next_point where #point_connection_temp.next_point is null order by a.key_fld I'm getting an error message:Incorrect syntax error near AS Any help will be greatly appreciated.Thank you!!!!!!!!!1

OPENQUERY UPDATE Syntax Help Needed bytes.com

Hi All I am updating a local table based on inner join between local table and remote table. Update LocalTable SET Field1 = B.Field1 FROM LinkedServer.dbname.dbo.RemoteTable B INNER JOIN LocalTable A ON B.Field2 = A.Field2 AND B.Field3 = A.Field3 This query takes 18 minutes to run. I am hoping to speed up the process by writing in OPENQUERY syntax. Thanks RS

How Do You Assign A Data Source To A Page And Which Type Sqldatasource, Objectdatasource Or ? forums.asp.net

Hi Using ASP.NET 2.0, Sql Server 2005. I have a simple page (NOT a formview) with some entries textbox's , checkbox and dropdownlistbox's I want to link a datasource to the 'Item  Page' and bind the datasource's values to the page The select statement is Select a.IssueID, a.ProjectID, a.VersionID, a.toincludeversionid, a.Version, a.toincludeversion, a.TypeofEntryID, a.PriorityID, a.WorkFlowID, a.Title, a.Area, a.Details, a.Question, a.Answer, a.HowToRepro, a.DevelopersNotes, a.TestersNotes, b.ProjectID, b.ProjectName, OldVersion.Version, ToIncludeVersion.Version, d.DESCRIPTION, e.DESCRIPTION,   x.TaskID as TaskID, x.DESCRIPTION as TaskDescription, z.Taskdone, CONVERT (char(9),z.TaskAssignedDate, 3) AS Workflowdate, z.StaffID as StaffID, w.username, y.latest_workflowid from issue as a Inner join ProjS b on b.ProjectId=a.ProjectID Left Outer join Version OldVersion on a.VersionID=OldVersion.VersionID Left Outer join Version ToIncludeVersion on a.VersionID= ToIncludeVersion.VersionID Inner join TypeOfEntry d on d.TypeOfEntryID=a.TypeofEntryID Inner join Priority e on e.PriorityID=a.PriorityID   inner join workflow z on z.issueid=a.issueid Inner join (select issueid,max(workflowid) as latest_workflowid from workflow group by issueid) y on y.latest_workflowid=z.workflowid Inner join task x on  x.taskid=z.taskid Inner join staffls w on w.StaffID=z.StaffID   Where a.IssueID= @IssueID     I hope I have made query clear, if not I don't mind explaining more.   Thanks in advance  

MySQL Query Gives Error: Unexpected T_CONSTANT_ENCAPSED_STRING? stackoverflow.com

I know this error is usually a problem with unclosed quotes and such, but I can't seem to find the problem here. I suspect it's something to do with the parentheses, who knows though. The error:Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING The query:$result = @mysql_query('select * from quotes inner join game on game.id=quotes.game_id inner join person on person.id=quotes.speaker_id where game.id = ' . $gameid . ' and person.id in (SELECT person.id FROM person[code]....

Whats Wrong With My SQL String - **Resloved** www.vbforums.com

I am getting the following error when I execute this SQL String: Incorect syntax near the keyword "INNER". I am attempting to update the City and State fields in the tblPersonnel with the City and State Fields from tblZipCode. I am Joining on the ZipCode Field in both tables. strSQL = "UPDATE tblPersonnel INNER JOIN tblZipCode ON tblPersonnel.Addr_Zip = tblZipCode.Zip " strSQL = strSQL & "SET tblPersonnel.Addr_City = [City], tblPersonnel.Addr_State = [State];"Thanks!

Sql Statement Error www.vbforums.com

hi guys - pulling my hair out with this one...Code: Dim oRs As ADODB.Recordset Dim strSQLString As String strSQLString = "SELECT" strSQLString = strSQLString & " customer.*," strSQLString = strSQLString & " Hardware.hardwaretype," strSQLString = strSQLString & " Hardware.hardwaredesc," strSQLString = strSQLString & " Hardware.hardwareprice," strSQLString = strSQLString & " Invoiced.TransactionDate," strSQLString = strSQLString & " Invoiced.TransactionAmount," strSQLString = strSQLString & " Invoiced.TransactionNumber" strSQLString = strSQLString & " FROM" strSQLString = strSQLString & " Hardware" strSQLString = strSQLString & " INNER JOIN" strSQLString = strSQLString & " (customer" strSQLString = strSQLString & " INNER JOIN" strSQLString = strSQLString & " Invoiced ON Customer.CustomerID = Invoiced.CustomerID " strSQLString = strSQLString & " ON hardware.hardwareidID = Invoiced.hardwareID)" strSQLString = strSQLString & " WHERE (Invoiced.TransactionNumber = " & InvToFind & ")"im getting syntax error on the where line..anyone know where im going wrong?fenster.

2 Records Not Showing In Report But They Can Be Retrieved From DB Using Same SQL Statement social.msdn.microsoft.com

Hi, I have a report with 5 filters which can be applied to it. The records are grouped by the Rotation programme there are on, with a subtotal for each unique programme name. The report seems to work fine, but upon closer inspection - we noticed that 2 of the records are not being displayed. As a result, the total count is out by 2. We tracked down the missing records so I ran the SQL query with a Where clause, and it was able to find the two records. What could possibly cause this behaviour? Please see included SQL statement : Code Block SELECT����� Posts.PostNumber,COUNT(Posts.PostNumber) AS RPCount, Incumbents.Name AS IncumbentName, Grades.GradeTitle, Specialties.SpecialtyTitle, ��������������������� Hospitals.Name AS Hospital, Genders.Gender, [Incumbent History].YearGraduated, COUNT([University Origins].Origin) AS OriginCount, ��������������������� Incumbents.Nationality AS NationalityID, Countries.[Country Name] AS Nationality, [Rotation Programmes].[Programme Name], ��������������������� Posts.[Post Approved for Training], [University Origins].Origin, Posts.OldPostNumber, [Rotation Programmes].[Programme ID] FROM�������� Posts INNER JOIN ��������������������� Incumbents ON Posts.PostNumber = Incumbents.PostNumber INNER JOIN �������������������� [Incumbent History] ON Incumbents.[Incumbent ID] = [Incumbent History].IncumbentID INNER JOIN ��������������������� Grades ON Incumbents.[Official Grade] = Grades.GradeID INNER JOIN ��������������������� Specialties ON Posts.Specialty = Specialties.SpecialtyID INNER JOIN ��������������������� Hospitals ON Posts.HospitalID = Hospitals.[Hospital ID] INNER JOIN ��������������������� Genders ON Incumbents.GenderID = Genders.GenderID INNER JOIN ��������������������� Countries ON Incumbents.[Country Of Birth] = Countries.[Country ID] INNER JOIN ��������������������� [Rotation Programmes] ON Posts.[Rotation Programme] = [Rotation Programmes].[Programme ID] INNER JOIN ���������������������� [University Origins] ON [University Origins].[Uni Origin ID] = Incumbents.[University Origin] GROUP BY [Rotation Programmes].[Programme Name], Posts.PostNumber, Incumbents.Name, Grades.GradeTitle, Hospitals.Name, Genders.Gender, ��������������������� [Incumbent History].YearGraduated, [University Origins].Origin, Incumbents.Nationality, Countries.[Country Name], [University Origins].Origin, ��������������������� Posts.[Post Approved for Training], Posts.OldPostNumber, Specialties.SpecialtyTitle, [Rotation Programmes].[Programme ID]

Getting Data From The Middle Of A String www.xtremevbtalk.com

I have a sql string and I need to split it out and put it into different variables. strSqlSELECTstrSqlFROMstrSqlWHEREIn some instances in my FROM clause I use alias' for table names. If I do this then I need to put the alias name and the real table name into an array. I get the alias name into a variable but I am having trouble getting the real table name. Here is some of my code:strSqlFROM = FROM ((((Assembly INNER JOIN [Assembly Top] ON Assembly.[Assembly ID] = [Assembly Top].[Assembly ID]) INNER JOIN Products ON [Assembly Top].[Product ID] = Products.[Product ID]) INNER JOIN Products AS Sub_Products ON Assembly.[Sub Product ID] = Sub_Products.[Product ID]) LEFT JOIN Projects ON [Assembly Top].Project = Projects.ProjectID) LEFT JOIN Subs ON Sub_Products.[Sub No] = Subs.[Sub No] arySqlAlias(0, 0) = Mid(strSqlFROM, InStr(1, UCase(strSqlFROM), " AS ") + 4, InStr(InStr(1, UCase(strSqlFROM), " AS ") + 4, UCase(strSqlFROM), " ") - InStr(1, UCase(strSqlFROM), " AS ") - 4)This is the alias name:arySqlAlias(0, 0) = Sub_ProductsI can't figure out how to get the real table name. How do I get the text before 'AS'?

Build SQL String Syntax Error www.utteraccess.com

Hi Group, I can't seem to find the missing operator problem in this code. I get a run-time error of 3075 indicating a syntax error. It happens when I added the ORDER BY string expression to this function to build a querydef. Can anyone spot my syntax problem? When I remove this part of the function: strSQL = strSQL & "ORDER BY " & strORDER the query is generated and not a problem. Thanks. ========== Private Function BuildSQLString(strSQL As String) As Boolean Dim strSELECT As String Dim strFROM As String Dim strWHERE As String Dim strORDER As String strSELECT = "tblSite.Site, tblFac.Fac, tblFac.FacName, tblFlr.FlrSortSeq, tblFlr.Flr, tblSpa.Room, tblUse.UseCode, tblUse.UseName, tblSpaC1.SpaC1Name, tblOrg.Org, tblOrg.OrgName, tblOrg.OrgTxt2, tblSpa.SpaDwg, tblSpa.SpaceArea, tblOrgAsgn.OrgPer, ([tblOrgAsgn].[OrgPer]*[tblSpa].[SpaceArea]/100) AS AsgnArea, tblFac.FacID, tblFlr.FlrID, tblUse.UseID, tblOrg.OrgID, tblSpaC1.SpaC1 " strFROM = "(tblMjUse INNER JOIN tblUse ON tblMjUse.MjUseID = tblUse.MjUseID) INNER JOIN ((tblSpaC1 INNER JOIN (tblSite INNER JOIN ((tblFac INNER JOIN tblFlr ON tblFac.FacID = tblFlr.FacID) INNER JOIN tblSpa ON tblFlr.FlrID = tblSpa.FlrID) ON tblSite.SiteID = tblFac.SiteID) ON tblSpaC1.SpaC1 = tblSpa.SpaC1) INNER JOIN (tblOrg INNER JOIN tblOrgAsgn ON tblOrg.OrgID = tblOrgAsgn.OrgID) ON tblSpa.SpaID = tblOrgAsgn.SpaID) ON tblUse.UseID = tblSpa.UseID " strORDER = "tblFac.Fac, tblFlr.FlrSortSeq, tblSpa.Room " If chkFacID Then strWHERE = strWHERE & " AND tblFac.FacID = " & cboFacIDLK End If If chkFlrID Then strWHERE = strWHERE & " AND tblFlr.FlrID = " & cboFlrIDLK End If If chkUseID Then strWHERE = strWHERE & " AND tblUse.UseID = " & cboUseIDLK End If If chkSpaC1ID Then strWHERE = strWHERE & " AND tblSpaC1.SpaC1 = " & "'" & cboSpaC1IDLK & "'" End If If chkOrgID Then strWHERE = strWHERE & " AND tblOrg.OrgID = " & cboOrgIDLK End If strSQL = "SELECT " & strSELECT strSQL = strSQL & "FROM " & strFROM If strWHERE <> " " Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6) strSQL = strSQL & "ORDER BY " & strORDER BuildSQLString = True End Function ===============

Tree Selection www.sqlteam.com

SELECT a.Network_ID, b.Last_Name + ', ' + b.Preferred_FirstName AS full_name, c.Security_Class_Description, d.Security_Type_Description, a.Security_Value FROM Company_Hierarchy_Security a JOIN V_Entity_Employee_Active b on a.Network_ID= b.Network_ID JOIN Company_Security_Class c on a.Security_Class_Code=c.Security_Class_Code JOIN Company_Security_Type d on a.Security_Type = d.Security_Type inner join (select e.Budget_Center_ID + ' - ' + e.Budget_Center_Description As Budget_Center_Description, f.Company_Name, g.Enterprise_Description, h.Business_Segment_Description, i.Team_Description from Company_Hierarchy_Security a. Inner JOIN Budget_Center e on a.Security_Value = e.Budget_Center_ID Inner JOIN Company f on a.Security_Value = f.Company_ID Inner JOIN Enterprise g ON a.Security_Value = Cast(g.Enterprise_Number As Varchar(5)) Inner JOIN Business_Segment h on a.Security_Value = h.Business_Segment_ID Inner JOIN Team i on a.Security_Value = i.Team_ID Ok. I have the Security Value located in Company_Hierarchy_Security table. All those values are divided into 5 other tables that I need to join together. I found all of them separate but I have not been able to figue it out how to put it together with the rest of the querie. Thanks for the help!!!!!