Hi guys, I'm still new around here and still a noob for sql. Can you give me some example for some joins from the easy ones to the most complex and all of it kinds, I heard that there's a lot of different kind of join in sql. You can give me some link or maybe some code examples for me, I appreciate all kinds of help . Thanks for all your help.
Regards.
Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:
INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName
OR
WHERE f.Name = @FacilityName
My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?
Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?
Thanks!
table_a has patient_id, tran_id and other fields a,b,c
table_b has patient_id,tran_id, key_id
and other fileds d,e,f
table_a patien_id + tran_id is unique
table_b patient_id + tran_id is not unique, could be duplicated.
I have to create a query which will retrieve fields from table a a,b,c and fields d,e from table b where
table a. patient_id + tran_id =
tableb.patient_id + tran_id and table_b.key_id is the min key_id for that patient_id + tran_id.
I should retrieve just one record.
How would I be able to do that?
Please help!!
Thanks
Hey all,
I have 3 tables: 1 table with Module information (1 quiz-like part of an Exam), 1 table with the relationships between Modules and Exams (ratios) and 1 table with User information, that contains the Exam number, Module number, and score. I am trying to get a full list of modules' names for 1 exam, and the user's score for those modules they taken. All this would pertain to 1 single Exam. I am only getting records returned for those modules that the user has taken; I want the full list with NULL values for the scores of modules the user has not taken. Hope that made sense, here is my attempt:
Code:
SELECT ExamModules.Name, ExamUsers.Score
FROM ExamToModule LEFT OUTER JOIN
ExamModules ON ExamToModule.ModuleID = ExamModules.ID LEFT OUTER JOIN
ExamUsers ON ExamToModule.ModuleID = ExamUsers.MID AND ExamToModule.ExamID = ExamUsers.EID
WHERE (ExamToModule.ExamID = 1) AND (ExamUsers.UserName = 'dizzle')
In this case the user is Dizzle and the Exam's ID (primary key) is 1. I've changed LEFT to RIGHT and FULL, they all return the same few records. Help?
I need a little help and I hope I can write a clear description of the problem. I have to tables, customers and custrate. The customer table simply has a custid of other misc info. The custrate contains an entry for each rate for any particular customer (1 customer to many rates). However the rates are suppossed to come in pairs. For example, if a customer has a rate id of 120 then the customer should also have a corresponding custrate record with a rate_id of 200. If the customer has a rate_id of 130, then the customer should have a corresponding custrate record containing rate_id 300, and so on and so forth. How do I find the customer id with are missing the corresponding rate? Here's an example or what I'm trying to explain.customerid | name1 | fred 2 | tom3 | eric 4 | fredcustratecust_id | rate_id1 | 1201 | 2002 | 1302 | 3003 | 1204 | 130I looking for the sql statement that would return customer id of 3 and 4, since those are the only 2 records that don't have a corresponding rate. I have hunch that it requires a intra table join, but maybe i'm totally wrong. Can anyone help me please? I would greatly appreciate it!
View 8 Replies View RelatedThis is a very complex query and i have tried everything with no sucess.
I'm having 3 Tables,
Orders which is having fields like CustomerID, ManufactureID, MerchID :- These all ID Fields (around 6) are foreign key of Contacts and Address Tables.
Address Table is having AddrID(Primary Key), ShortName
Contacts is having ContID (Primary Key), AddrID (Foreign Key), PersonName
I want to retreieve info in a single query which can return a single row with following columns :-
OrderNo, CustomerName, ManufactureName, MerchName etc. (all 6 columns) by joining these 3 tables
can anybody help in this.
If i want to do another join with this select join query, how would I do this I tried many different way, but keep failing or not getting the right answer.
I want to Inner Join tbSucess with the query below. Means that only return the result if the history does exists in tbSucess.
And
In a seperate select query.
I, I want to get only the records which are not found in tbSuccess . So how would I join those two.
Select r.History, LastName, FirstName, Convert(varchar(10), Pay, 101) as Pay,
from #rpt r inner join #history h on r.History = h.History
order by r.History, pay
I have a couple tables that I am exporting data from.
I have one table (call it TABLE1) that contains abbreviations instead of actual words.
For example instead of having the full word "New York City", the column would just contain "NYC".
In another table (call it TABLE2) it has all the abbreviations and the actual words.
So in TABLE2 one column name is FieldName, another Abbreviation and another is Value.
So on a web site, to display that actual name I do something like this
Code:
SELECT CityBoxlabel AS City
FROM TABLE1 INNER JOIN
TABLE 2 AS CityBox ON ISNULL(TABLE1.City, 'NYC') = CityBox.[Value]
WHERE CityBox.FieldName = 'City'
This is working great except that some of these columns contain more than one abbreviation such as "NYC,WDC"
which would stand for "New York City" and "Washington DC"
The items that have more than one abbreviation are not being pulled across because my query is looking for an "NYC,WDC" in TABLE2 to INNER JOIN on but it won't be in there. But "NYC" and "WDC" by themselves are in there.
I tried messing with the INNER JOIN statement by saying something like
TABLE2 AS CityBox ON ISNULL(TABLE1.City, 'NYC') IN CityBox.[Value] INNER JOIN
But it wasn't allowing that.
I can write a program to do this, but I would rather keep it in T-SQL.
Hope this wasn't too confusing!
Any help is greatly appreciated!!
hi thereanyone had an idea to join following 2 queries to 1????----- QUERY 1 ---------------------------------------------SELECT TMS_CaseF_2.Name AS TCDomain_0,TMS_CaseF_3.Name AS TCDomain_1,TMS.CaseF.Name AS TCFolder_2,TMS_CaseF_1.Name AS TCFolder_3,TMS.TestCase.Name AS TCName_4,TMS_TestCase_1.Name AS TCName_5,TMS.LogFolder.Name AS PlannedLogFolder_6,TMS.Log.Name AS PlannedLog_7,TMS.CaseResult.RecordedCaseName AS TCRecordedName_8,TMS.TestPlan.Name AS Plan_9FROM((((((((((TMS.Build INNER JOIN TMS.LogFolder ON TMS.Build.UID =TMS.LogFolder.Build)INNER JOIN TMS.Log ON TMS.LogFolder.UID = TMS.Log.LogFolder)INNER JOIN TMS.CaseResult ON TMS.Log.UID = TMS.CaseResult.Log)INNER JOIN TMS.TestCase ON TMS.CaseResult.TestCase =TMS.TestCase.UID)LEFT JOIN TMS.CaseF ON TMS.TestCase.Parent = TMS.CaseF.UID)LEFT JOIN TMS.TestCase AS TMS_TestCase_1 ON TMS.TestCase.Parent =TMS_TestCase_1.UID)LEFT JOIN TMS.CaseF AS TMS_CaseF_1 ON TMS_TestCase_1.Parent =TMS_CaseF_1.UID)LEFT JOIN TMS.CaseF AS TMS_CaseF_2 ON TMS_CaseF_1.Parent =TMS_CaseF_2.UID)LEFT JOIN TMS.CaseF AS TMS_CaseF_3 ON TMS.CaseF.Parent =TMS_CaseF_3.UID)INNER JOIN TMS.TestPlan ON TMS.TestCase.TestPlan = TMS.TestPlan.UID)WHERE (((TMS.LogFolder.Name) Like 'TR1%')AND ((TMS.Build.Name)='Planning_VD10A'))ORDER BY TMS.CaseF.Name,TMS_CaseF_1.Name,TMS.TestCase.Name,TMS_TestCase_1.Name;------------------------------------------------------------------ QUERY 2 ---------------------------------------------SELECT TMS.CaseResult.RecordedCaseNameFROM ((TMS.Build INNER JOIN TMS.LogFolder ON TMS.Build.UID =TMS.LogFolder.Build)INNER JOIN TMS.Log ON TMS.LogFolder.UID = TMS.Log.LogFolder)INNER JOIN TMS.CaseResult ON TMS.Log.UID = TMS.CaseResult.LogWHERE (((TMS.LogFolder.Name) Like 'VD%')AND ((TMS.Build.Name)='VD10A IT_APP'));
View 1 Replies View RelatedTable A
Registation_key discharge_dt
1001 7/15/07
1002 6/4/07
1003 7/30/071
Table B
REgistration_key ID end_dt value
1001 879 07/11/07 1
1001 888 07/15/07 2
1002 777 06/01/07 5
1002 778 06/01/07 2
1002 779 06/30/07 6
1003 234 07/31/07 3
1003 234 07/31/07 1
1003 235 08/01/07 7
I am trying to join Table A to Table B using the below log . Table A should have one unique mathincg record from Table B
option 1. Using registration_key and discharge_dt , looking for exact matching date( end_dt) in table B , if there is more than one record that matches then select lowest ID
option 2.if there is no record that matches option1 then, serch for the previous record with end_dt in table A less than end_dt table B. if there is more than one record then select lowest ID
option 3. if there is no record option 2 then search the next record that matches .. discharge_dt greater than end_Dt , if there is more than one record then select lowest Id
so basically, I am looking for an exact matching date in the same registration_key .. if the exact dt doesn't exist looking for the previous record and get the most closer dt and if there is no prvious record than look for next record,.
so the output should look like
Table B
Registration_key ID end_dt discharge_dt value
1001 888 07/15/07 7/15/07 2
1002 778 06/01/07 6/4/07 2
1003 234 07/31/07 8/1/07 7
How can I do this conditional join ?
I have Two tables @master and @child
Master Table :
MasterID EntryNumber BranchId IsstockIn
1 1 1 1
2 1 1 0
Child Table:
CEntryNumber CBranchID EntryQty
1 1 10
1 1 20
1 1 -5
1 1 -4
My Query:
Select SEC.EntryQty from Item.StockEntryChild SEC
where SEC.CEntryNo =
(
select SEM.EntryNumber from item.StockEntryMaster SEM
where SEC.CBranchID=SEM.BranchID and SEC.CEntryNo=SEM.EntryNumber and SEM.MasterID=1 and SEM.isStockIn=1
)
My Result:
EntryQty
10
20
-5
-4
Expected Result:
10
20
I have a very confusing/complicated query that I am trying to run and getting not the results that i want.
Essentially I have three tables (t1, t2, t3) and I want to select data from two of them, but there are conditions on the data where I need fields to match across pairs of tables.
When I run my select query I am getting far too many records - it's giving me all possible combinations, instead of the proper combinations that I want.
Select t1.*, t3.field2, t3.field3
FROM, t1, t2, t3WHERE t2.field4=t3.field4 AND t1.field5=x AND t1.field6=t2.field6
I suspect there is plenty wrong with this query - where should I start correcting it?
Here is the situation
Table 1 : tbl_documents
docIDdocName
1aaa
2bbb
3ccc
Table 2 : tbl_Rating
ratIDratingdocID
131
251
321
432
The queary I need is to display the result in this form. must be like this
docIDdocName Avaragerating
1aaa3
2bbb3
3ccc0
NOTE : For getting the average I used this queary “SELECT SUM(rating) As
RatingSum, COUNT(*) As RatingCount FROM tbl_Rating WHERE tbl_rating.docID =
tbl_documents.docID”
PLs help me ?
Thx
I had this (what seems to be a) simple question asked today and I'm afraid I didn't like my answer. Does anyone know the proper answer to this one:
Any ideas on how I can constrain a lookup or merge join based on the dimension row's effective and expired dates so three criteria are needed as follows:
1. DataStagingSource.ModifyDate < DataWarehouseDimension.RowExpiredDate AND
2. DataStagingSource.ModifyDate >= DataWarehouseDimension.RowEffectiveDate AND
3. DataStagingSource.NaturalKey = DataWarehouseDimension.NaturalKey
-- Brian
Hi,
I have a complex join filtering on a replicated sql server database which was working fine in previous versions of sql compact. The query is something like the following:
SELECT <published columns> FROM <filtered table> INNER JOIN <child table> ON <child table>.ID = <filtered table>.ID and <child table>.date > getdate()-30
After I upgraded to compact databse 3.5, for some weird reason whichever tables have both these Join filter and article filter together behaving improperly. If I insert any row in any of these table, the row is replicated properly to the server, but it does not send the new row to any other users. Again this thing works fine in older version. I have switched back tyo the old version of sql ce and again it's started working.
I have a few tables I am trying to join to create a report. Everything was working fine until I tried to add an aggregate Sum function to a column (MaxCap) in table ctfBarn.Â
select
x.*, y.division, y.department, y.location
,(right(z.SvcMgrName,len(z.SvcMgrName)-len(left(z.SvcMgrName,CHARINDEX(', ',z.SvcMgrName)-1))-2)+' '+
left(z.SvcMgrName,CHARINDEX(', ',z.SvcMgrName)-1))AS SvcMgrName
,(right(z.SrSvcName,len(z.SrSvcName)-len(left(z.SrSvcName,CHARINDEX(', ',z.SrSvcName)-1))-2)+' '+
[Code] .....
I think I probable need to include a group by but can't figure out the correct syntax.
Anyone know why using
SELECT *
FROM a LEFT OUTER JOIN b
ON a.id = b.id
instead of
SELECT *
FROM a LEFT JOIN b
ON a.id = b.id
generates a different execution plan?
My query is more complex, but when I change "LEFT OUTER JOIN" to "LEFT JOIN" I get a different execution plan, which is absolutely baffling me! Especially considering everything I know and was able to research essentially said the "OUTER" is implied in "LEFT JOIN".
Any enlightenment is very appreciated.
Thanks
Monitoring a sql 2000 server where a vendor app is running. Looking at a poor performing sql and I saw the following in the where clause
WHERE immunization_mast_.account_id =* p.account_id AND
immunization_mast_.case_id =* p.case_id
What is the meaning of the =*, have not seen this before?
Hi
I have two databases on my server, I need a simple query with one join between one table from each database.
I looked in the help of FROM clause and found the Argument "table_source" where it explains this :
"If the table or view exists in another database on the same computer running Microsoft® SQL Server™, use a fully qualified name in the form database.owner.object_name".
Can someone please help me fill the variants ??
My DB name is "Forum" the owner is "DBRNDAdministrator" and the table name is "TblUsers", so I tried to write in the FROM clause :
"FROM Forum.DBRNDAdministrator.TblUsers" but it doesn't work... so anyone have any idea how should it be ?
Thanks,
Inon.
Hello,
I need to write and Ansi Outer Join that has 2 columns from the same table.
I keep getting
Server: Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "dim_person.person_key" could not be bound.
here is what the code looks like. any help is appreciated.
select ...
from dim_day_of_year
, dim_company
, dim_currency
, dim_account
, dim_person
, ods_conference
left outer join fact_usage_alloc fua1 on ods_conference.conf_key=fua1.conf_key
left outer join fact_usage_alloc fua2
on dim_person.person_key=fua2.requestor_person_key
where...
I am trying to figure out some sql syntax, and I could use some help. Thisis my first atempt at joins, so bear with me.I have a table (A) which looks like the followingID Data Source-------------------------1 abcdef 1002 abcdef 1003 abcdef 2004 abcdef 2005 abcdef 200A second table (B) which looks like the followingKey ID------------------------Key1 1Key1 2Key1 3Key1 4Key2 1Key2 2Essentially, A is a table of items, and B is a table of where those itemshave been used (Key1 is like an invoice which has items 1-4 on it, Key2 is asecond invoice with 1 and 2.) Source, in table A, is like the itemsupplier.I would like to get a list of every invoice (Key) that has used a part (ID)from a particular Source.So, for example, I would like to query for source 100 and get back (Key1,Key2) or query for source 200 and get back only Key1.To this end, I tried"SELECT DISTINCT B.Key FROM B JOIN A ON (B.ID = A.ID) WHERE (A.Source =100)"But I got an empty recordset, so something is amiss.Any help is greatly appreciated.Thanks,-d
View 2 Replies View RelatedErland Sommarskog <sommar@algonet.se> wrote in messagenews:Xns93EFA9C57954AYazorman@127.0.0.1...[color=blue]> MAB (fkdfjdierkjflafdafa@yahoo.com) writes:[color=green]> > What I want is the sum of the amounts of the last payments of all> > customers. Now the last payment of a customer is not necessarily the one> > with the highest paymentid for that customer BUT it is the one with the> > highest paymentid on the MOST RECENT date. We dont keep the time part> > just the date so if there are more than 1 payments of a customer on a> > date ( and there are many such cases ) only then the paymentid decides> > which is the last payment. Further the last payment may be the last as> > of today but I may want to find the sum of all the last payments upto> > say March 1, 2003 or any date. My own solution is too slow even it is> > correct.[/color]>> This solution is not tested, as you did not provide any sample data:>> SELECT SUM(p3.amount)> FROM Payments p3> JOIN (SELECT paymentid = MAX(p2.paymentid)> FROM Payments p2> JOIN (SELECT p1.customerid, mostrecent = MAX(p1.date)> FROM Payments p1> WHERE p1.date <= '20030301'> GROUP BY p1.customerid) AS p1> ON p1.customerid = p2.customerid> AND p1.mostrecent = p2.date) AS p2> ON p3.paymentid = p2.paymentid>> This solution is for SQL Server only. I don't know Access, so I can't> help with that.>> As for performance, this is likely to be a case of finding the best> indexes. Clustered on (date, customerid) and nonclustered in (paymentid)> maybe.[/color]Thanks. This looks fascinating. It looks correct too (although I haventfully verified that). From this my next questionIs it possible to write your query in older join syntax likeFROM Payments p1, Payments p2WHERE p1.customerid = p2.customerid etc.Or is it that the newer syntax is superior such that you can do things withit that you cant do with the older one?I cant see how to write such a query the older way because you have only oneSELECT Clause thereMany thanks again
View 4 Replies View RelatedI have a query where I need to join a table to multiple tables and alias a field from those tables on each join. I tried the syntax below but received a error. Please assist, first time trying to do this.
JOIN dbo.AbsenceReason ar ON ar.AbsenceReasonID = sda.AbsenceReasonID
WHERE ar.[Name] = 'DailyReason'
LEFT JOIN dbo.AbsenceReason ON ar.AbsenceReasonID = spa.AbsenceReasonID
WHERE ar.[Name] = 'PeriodReason'
LEFT JOIN dbo.AbsenceReason ON ar.AbsenceReasonID = cio.AbsenceReasonID
WHERE ar.[Name] = 'CheckInOutReason'
error I receive is :
Msg 156, Level 15, State 1, Procedure p_000001_GetAttendanceProfileData, Line 45
Incorrect syntax near the keyword 'LEFT'.
Msg 102, Level 15, State 1, Procedure p_000001_GetAttendanceProfileData, Line 63
Incorrect syntax near ')'.
Hi,
Can anyone please tell me how can i convert the following query in
the old join syntax to the new join syntax so that it returns the same
results? I tried converting it, but it returns different reuslts.
please help me as it is urgent.
OLD Syntax:
===========
SELECT
....................
..<column_list>......
FROM
dbo.ASSET_BOOK AL1,
dbo.ASSET_BOOK AL2,
dbo.ASSET_HEAD AL3,
dbo.ASSET_BOOK AL4,
dbo.ASSET_BOOK AL5
WHERE
(AL1.U##ASSETNO =* AL3.U##ASSETNO
AND AL3.U##ASSETNO *= AL2.U##ASSETNO
AND AL4.U##ASSETNO =* AL3.U##ASSETNO
AND AL3.U##ASSETNO *= AL5.U##ASSETNO)
AND
(AL1.U##BOOKCODE = 'USGAAP'
AND AL2.U##BOOKCODE = 'CONSUSD'
AND AL4.U##BOOKCODE = 'ICP'
AND AL5.U##BOOKCODE = 'STETC'
)
New Syntax:
===========
SELECT
COUNT(*)
FROM dbo.ASSET_BOOK AL1
RIGHT JOIN dbo.ASSET_HEAD AL3
on AL1.U##ASSETNO = AL3.U##ASSETNO
LEFT JOIN dbo.ASSET_BOOK AL2
on AL3.U##ASSETNO = AL2.U##ASSETNO
RIGHT JOIN dbo.ASSET_BOOK AL4
on AL4.U##ASSETNO = AL3.U##ASSETNO
LEFT JOIN dbo.ASSET_BOOK AL5
on AL3.U##ASSETNO = AL5.U##ASSETNO
AND
AL1.U##BOOKCODE = 'USGAAP'
AND AL2.U##BOOKCODE = 'CONSUSD'
AND AL4.U##BOOKCODE = 'ICP'
AND AL5.U##BOOKCODE = 'STETC'
Thanks in advance.
Rafi
Hi,
Trying to join two tables together.
Select * from ...
inner join
on table1.ID = table2.ID;
this works fine, however i want to use the RIGHT function so that i get the last four digits of the ID. this causes a parse error:
inner join on table1.right([ID],4) = table2.right([ID],4);
searched a bit but couldn't find a way to do what im trying to accomplish. is there a proper syntax?
thanks!
Is there a way to join 2 tables from different databases on the same instance? If so, what is the syntax? For example I have an instance called TEST with 2 databases (DB1 and DB2). Each database have a table (DB1.TABLE & DB2.TABLE). Both tables have a common column called ID.
Can both tables be joined in a query?
TIA.
I am getting the error message:
Msg 156, Level 15, State 1, Line 62
Incorrect syntax near the keyword 'inner'.
Msg 102, Level 15, State 1, Line 62
Incorrect syntax near 'Code'.
When I attempt to run the following query:
select NewUsed, VehicleYear, VehicleMake, VehicleModel, VehicleTrim,
AppZip, ltrim(newtrademake) AS TradeMake, ltrim(trademodel)TradeModel,
TradeYear, NcodeL AS TradeNcodeL, NcodeM AS TradeNcodeM, OwingOnTrade, TradeAllowance, NetTradeIn
from
(select *,
Case
When TradeMake = ' Ford' and (Trademodel like '%Aerostar%' or Trademodel like '%Bronco%')
Then 'FORD TRUCKS'
Else ltrim(TradeMake)
End
AS NewTradeMake
from dbo.vw_dds)
inner join (select distinct make, model, ncodel, ncodem, modelyear from us.dbo.algmaster) Code
on code.make = ltrim(newtrademake) and code.model= ltrim(trademodel) and code.modelyear = tradeyear
The error is related to the inner join but I can't seem to figure out what I am doing wrong.
Thank you for your help,
Tasha
I inherited a query and I am getting an error of Unsupported literal in join in the INNER JOIN FRDM.dbo.MEMBER_SUBSC FRDM_dbo_MEMBER_SUBSC2
ON (frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.SBSB_CK = FRDM_dbo_MEMBER_SUBSC2.SBSB_CK AND FRDM_dbo_MEMBER_SUBSC2.MEME_SFX
= '00')statement. Specifically the AND FRDM_dbo_MEMBER_SUBSC2.MEME_SFX
= '00')part.
SELECT frdm.dbo.CLAIM_HEADER_WITH_VOIDS_VIEW.GRGR_ID AS 'group number'
[code]....
what is the syntax to join a table with the result of antoher query.For example i have two tablesCreate Table Customers (CustomerID int,LastPaymentDate Datetime )Create Table Payments (PaymentID int,CustomerID int,PaymentDate Datetime )What query will bring me the customers whose lastpaymentdate in thecustomers table is not correct.That can only be checked by comparing it with the max paymentdate for eachcustomer in the payments table.I want this to be done by ansi standard sql. Not using any specific featureof sql server.thx
View 11 Replies View RelatedI am now working on SQL Server 2000 having had previous experience ona different database. Both of the OUTER JOIN syntaxes is differentfrom what I am used to and I am finding it slightly confusing.For example, given two tables :wipm_tbl_miwipm_tbl_wi (which may not have data in it for a specific record thatexists in the first table.)If I use the old style syntax :SELECT mi.workitemid, wi.datavalueFROM wipm_tbl_mi mi , wipm_tbl_wi wiWHERE mi.workitemid *= wi.workitemidAND mi.workitemid = 1AND wi.dataname = 'XXX'I get back1,NULLwhen there is no matching record in wipm_tbl_wi, which is what Iexpect.However, if I try to use the SQL-92 syntaxSELECT mi.workitemid, wi.datavalueFROM wipm_tbl_mi miLEFT OUTER JOIN wipm_tbl_wi wiON mi.workitemid = wi.workitemidWHERE mi.workitemid = 1AND wi.dataname = 'XXX'I don't get anything back. Please can someone help me understand whatis wrong with the bottom query.Thank you,Martin
View 2 Replies View RelatedI apologize if this has been asked before- I searched google but couldnot find a concrete answer.I recently inherited a database whose t-sql code is written in a formatthat I find difficult to read (versus the format I have used foryears).I have tested the queries below using the SQL Profiler, and both haveidentical costs. Is there any advantage of one format over the other?Format 1:---------SELECT *FROM Customers c, Orders oWHERE c.CustomerID = o.CustomerIDFormat 2:---------SELECT *FROM Customers cINNER JOIN Orders AS o ON c.CustomerID = o.CustomerIDIs it just a matter of personal preference? Does the same hold true forusing OUTER JOIN versus the old *= or =* ?Thanks,Matt
View 3 Replies View RelatedTeam,
Our product was written against an older version of SQL and the non-ANSI standard join operators (*= and =*). Our group is now looking to move our database to SQL Server 2005.
We are now faced with updating a lot of legacy queries that use this method of joins and I was hoping for a shove in the right direction.
I have run across a query similar to the following:
SELECT <many fields go here>
FROM a, b, c, d, e, f, g, h
WHERE a.fld1 *= d.fld1
AND e.fld3 *= h.fld3
The query, as it is with the non-ANSI join operators, returns a single record. For the life of me, I cannot figure out how to format the FROM statement to create the LEFT OUTER JOIN statement(s) to get the query to work.
The SQL Server 2005 books online seems to be lacking in the area of examples where you have multiple joins using different tables; they focus on one table joining against multiple tables instead.
Anyone have any reading suggestions or other ideas on how to get this to work?
Thanks.
Richard
I'm looking at upgrading to SQL2005 and have found the following issue with some old t-sql we have.
We have some views that are created using the old outer join syntax of *=. If I run the SQL in query analyzer, it runs fine...but if I create a view with the SQL, when I query the view I get the error:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I tried changing the database compatibility level all the way back to 7.0, and recreating the view (still using *=), but still had the same error.
Is it possible to have a view that uses this old syntax with SQL2005? How? I don't want to have to find/rewrite everything very quickly.
thanks,
Matt