SELECT LEFT(ZZZT1..gl00105.actnumst, 15) As ZZZT1_
Apr 18, 2008
My sql statement is running fine in SQL 2000 but it I got the error when running it in SQL2005. The error is "Msg 1013, Level 16, State 1, Line 1
The objects "AL013..gl00105" and "ZZZT1..gl00105" in the FROM clause have the same exposed names. Use correlation names to distinguish them."
This is the SQL statement:
SELECT LEFT(ZZZT1..gl00105.actnumst, 15) as ZZZT1_actnumst,
LEFT(AL013..gl00105.actnumst,15) as AL013_actnumst
FROM ZZZT1..gl00105
RIGHT OUTER JOIN AL013..gl00105 ON
ZZZT1..gl00105.actnumst=AL013..gl00105.actnumst
where ZZZT1..gl00105.actnumst is null
--Checks the reverse relationship
SELECT LEFT(ZZZT1..gl00105.actnumst, 15) as ZZZT1_actnumst,
LEFT(AL013..gl00105.actnumst, 15) as AL013_actnumst
FROM AL013..gl00105
RIGHT OUTER JOIN ZZZT1..gl00105 ON
AL013..gl00105.actnumst=ZZZT1..gl00105.actnumst
The select command below will output one patient’s information in 1 row:
Patient id Last name First name Address 1 OP Coverage Plan 1 OP Policy # 1 OP Coverage Plan 2
[code]...
This works great if there is at least one OP coverage.  There are 3 tables in which to get information which are the patient table, the coverage table, and the coverage history table.  The coverage table links to the patient table via pat_id and it tells me the patient's coverage plan and in which priority to bill.  The coverage history table links to the patient and coverage table via patient id and coverage plan and it gives me the effective date. Â
select src.pat_id, lname, fname, addr1, max(case when rn = 1 then src.coverage_plan_ end) as OP_Coverage1, max(case when rn = 1 then src.policy_id end) as OP_Policy1,
i am having names like AB_12 I want to get all rows with left part similar , AB im that case
SELECT id, name FROM Users WHERE LEFT(name, CHARINDEX('_', name) - 1) AS name IN ( SELECT LEFT(name, CHARINDEX('_', name) - 1) AS ns FROM Users GROUP BY LEFT(name, CHARINDEX('_', name) - 1) HAVING (COUNT(*) > 1) )
does not work
is there any way to use a variable ?
declare @nm nvarchar set @nm = SELECT LEFT(name, CHARINDEX('_', name) - 1) AS ns FROM Users
Hi again, I have this SQL (part of a stored procedure) where I do LEFT JOIN. SELECT callingPartyNumber, AlertingName, originalCalledPartyNumber, finalCalledPartyNumber, dateTimeConnect, dateTimeDisconnect, CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108) AS duration, clientMatterCode
FROM CDR1.dbo.CallDetailRecord t1 LEFT JOIN CDR2.dbo.NumPlan t2 ON t1.callingPartyNumber=t2.DNorPattern
WHERE (t1.callingPartyNumber LIKE ISNULL(@callingPartyNumber, t1.callingPartyNumber) + '%') AND (t1.originalCalledPartyNumber LIKE ISNULL(@originalCalledPartyNumber, t1.originalCalledPartyNumber) + '%') AND (t1.finalCalledPartyNumber LIKE ISNULL(@finalCalledPartyNumber, t1.finalCalledPartyNumber) + '%') AND (t1.clientMatterCode LIKE ISNULL(@clientMatterCode, t1.clientMatterCode) + '%') AND (@callerName is NULL OR t2.AlertingName LIKE '%' + @callerName + '%') AND (t1.duration >= @theDuration) AND ((t1.datetimeConnect) >= ISNULL(convert(bigint, datediff(ss, '01-01-1970 00:00:00', @dateTimeConnect)), t1.datetimeConnect)) AND ((t1.dateTimeDisconnect) <= ISNULL(convert(bigint, datediff(ss, '01-01-1970 00:00:00', @dateTimeDisconnect)), t1.dateTimeDisconnect)) The problem is that if the t2 has more than one entry for the same DNorPattern, it pulls the record more than once. So say t1 has a callingPartyNumber = 1000. t2 has two records for this number. It will pull it more than once. How do I get the Unique value. What I am trying to get is the AlertingName (name of the caller) field value from t2 based on DNorPattern (which is the phone number). If this is not clear, please let me know. Thanks, Bullpit
I think it is quite often when you need to view some records, which refer (by key) to data in other tables. For instance, a user belongs to a group but it is preferable to show group name in the user data rather than group id. The options are
1) LEFT OUTER JOIN: SELECT users.id, groups.name FROM users LEFT OUTER JOIN groups ON users.[group] = groups.id
2) A Subselect: SELECT id, [group] = (SELECT [name] FROM groups WHERE id = users.[group]) FROM users
I have an application providing me with multiple headers which I havemergerd into one big header (below), this header my not always be thesame but I need to be able to extract a periodstart and periodend fromit. The periodstart will always be the third substring from the end(or 3rd from right) and the periodend will always be the firstsubstring from the end (or 1st from the right).How can I extract the periodstart and periodend?E.g:- Header'Jensen Alpha TR UKN GBP BM: Caut Mgd BM (50% FTAllSh 50% ML £ BroadMkt) RF DEF:RFI 3Y 31/08/2004 To 31/08/2007'I currently have the sql: convert(Datetime,(dbo.FDHGetWord(@FullHeader, 20)) ,103) but this only works in thisinstance, I need to use someting like the RIGHT function or REVERSEfunction but I can't get the sql right.Can someone please help!????
 Users // table  UserID // pk  UserName // varchar UserFamilyName // varchar User_Friends // table  FriendsID // pk  UserID // fk  FamilyName // varchar
MY query:
 SELECT U.UserFamilyName, F.FamilyName  FROM  Users U LEFT JOIN User_Friends F ON U.UserID = F.UserID  WHERE     U.UserName = ‘JOHN’
How do I adjust my query to select just the very first record from Users_friends, I want only the top first one.And if there are no friends how can I return an empty string instead of Null.
I need to left pad the column with 0 if it is less than 4 characters long and extract the first 2 characters on the left into a new column COUNTY_CODE.
How can I do that in transact SQL?
I tried: Â Â Â Â SELECT Â RIGHT(RTRIM('0000'+ISNULL([Code],'')),4) Â Â Â Â Â FROM [Place] Â Â WHERE [Place Code]='B' and [Code]='627'
And I got 0627. And how do I extract the first 2 characters?
How to remove space left to right and right to left
If I give limit >60 for first 60 character; limit 60< second 60 character
Result would be check if space at 60 character if yes remove and go the 59 character check then space remove and 58 character check if there is charater then display
As well as after 60 character to till 120 for right space
Why would I use a left join instead of a inner join when the columns entered within the SELECT command determine what is displayed from the query results?
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".
Im having a problem with a statement i cannot seem to get 2 left joins working at the same time 1 works fine but when i try the second join i get this error:-
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx'.
My SQL statment is as follows :- SELECT children_tutorial.*,schools.schoolname,regions.rname FROM children_tutorial LEFT JOIN schools ON children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx
I am using an Access database i have tried all sorts to get it working and its driving me mad!! any help would be really appreciated.
how can I can update a table :: I must get all the left part of a columns part1_part2I dont know how many characterssomething like :UPDATE Users SET Users.Info = left(Users.name, _ ??? or split('_'))in that case Users.name = part1i must update only the one with a '_' and do nothing for the othersthank you
I am trying to pad a field from the left with zeros that is defined in a table as "[char] (9)". The field can have from 1 to 9 chars populated. In this instance the value in the field is "133000", which I am trying to pad so that it will be "000133000". I have made several failed attempts to do this, the latest of which was this:
select right(replicate('0',9)+ convert(varchar(9),a.GS06GRP_CTL_NBR),9) fromTable1 a
I'm trying to join 2 tables. I thought I was getting the correct results but it turns out I'm not. My Query: SELECT IVINVA, IVORDN, IVCSLN, IVRESR, IVCITM, CONVERT(varchar(12),CAST(IVIAMT as money),1) AS ExtPrice, CONVERT(varchar(12),CAST(IVPIVC as money),1) AS DistPrice, IVCSUM, IVQYCS, IVDESC, OIRESR, OIDPCT, CONVERT(varchar(12),CAST(IVPIVC - (OIDPCT / 100 * IVPIVC) as money),1) AS NetPrice FROM INVDET1_TBL LEFT JOIN ORDDIS_TBL ON ORDDIS_TBL.OIORDN = INVDET1_TBL.IVORDN AND ORDDIS_TBL.OIRESR = INVDET1_TBL.IVRESR WHERE IVORDN = '0859919' AND IVINVA = '00324024' Basically, my problem lies in the seonc condition of the LEFT JOIN. I needed to set the two tables equal my item number, because in some situations I need that logic to get the correct result. It most other cases, that item column in the ORDDIS_TBL is NULL, thus giving me the wrong results. In that case, I would want the JOIN to only be ORDDIS_TBL.OIORDN = INVDET1_TBL.IVORDN, and not include the second part. Is there a way I can condition this with an If statement, If ORDDIS_TBL.OIRESR is Null then do this join, if not, then do this? I'm confused how to get the proper result here.
What is the syntax of left triming characters seperated by space.
e.g elsie reed and i want elsie to go to one field and reed in another field i do not want to use numbers because they don't all have the same numbers but are all seperated by spaces
This may be a very simple problem but it's been racking may brains for a while and I just can't seem to think it through clearly.
I'm trying to return a query which uses a left join and where. I'm hoping to get a result set which shows - let us says all the departments in a company. I would like to see all the department but only the names of department heads that earn 20.000+.
In MS Access I used a subquery. My subquery returned only departments with department heads that earned 20.000+ - I then left joined the departments table to that query - no problem.
With MSSQL I've tried IN, ANY, ALL but my result sets only returns the departments that earn 20.000+ and the employees for those particular departments.
I'm thinking there must be some way of doing this without having to use a union clause.
Thanks for taking the time to read this message through.
3) select p.komorka,isnull(sum(ustalenia),0) from #plantemp p left join analiza_1 a on p.komorka=a.komorka where a.koniec between '20040701'and '20040731' group by p.komorka
komorka ustalenia (sum)
08.0000 SI/1788138.9300 SI/262856.8900
I need all rows from table 1 bat right and left join gives me the same results, WHY
As you can see the ParentIndexID and ChildIndexID fields refer to tblIndices.IndexID I would like a stored procedure as follows: show all index names and show the wights for the indexID you passed. This is what I have so far and it is not correct yet. Not sure what the syntax should be.
alter PROCEDURE [dbo].[uspBasketIndices_Get]
@IndexIDint
AS
select i.IndexID, i.[Name], bc.Weight from tblIndices as i left join tblBasketConstituents as bc on i.IndexID = bc.ParentIndexID and i.IndexID = @IndexID order by i.[Name]
Any one know any facts and figures about maximum Left Joins allowed (or recommended) in one query?
I am running a MS SQL 2000 my database is full of relational data and most of my foreign keys (INT data type) are a Clustered Indexed, Usually I will only be pulling one record from collection of about a dozen tables, but the Database is expected to grow fast and become big.
Right now I have a Stored Proc that has eight(8) LEFT JOINs in it. My worry is that this query will kill me as the database approaches 50,000 records.
Hi all. My query works fine, it generates reports but not my expected result.
select d.fullname, p.nickname, p.birthdate, p.birthplace, p.gender, p.civilstatus, p.religion, p.nationality, p. weight, p.height, p.haircolor, p.eyecolor, p.complexion, p.bodybuilt, p.picture, p.dialectspoken, d.mobilephone, d.prprovince,[Age] = dbo.F_AGE_IN_YEARS( birthdate, getdate() ), c.name, c.address, c.telno, c.email, c.occupation, ed.year1, ed.year2, ed.degree, sch.schname from hremployees as e inner join psdatacenter as d on e.empdcno = d.dcno inner join pspersonaldata as p on e.empdcno = p.dcno left join hrappempcharrefs as c on e.empdcno = c.empdcno left join hrappempeducs as ed on e.empdcno = ed.empdcno left join hrsetschools as sch on ed.schoolcode = sch.schcode
the above query gives a 77 records
if i ran "select * from hremployees" generates 60 records
i think the error is in the left joining. hrappempcharrefs, hrappempeducs and hrsetschools must be left joined to hremployees.
and this give's us the string part AFTER the , so that part is correct!
but I can't find out how to do the same thing but then for using everything BEFORE the ,
...
anyone got an idea? just changing Right with Left give's us (for example): http://thisisa instead of http://thisisatest.com (he's counting the number of characters from the right to the , and then displays the characters starting from the left but only the number of characters he counted previously) ...
My understanding of relevant topics as well as SQL Books Online definition of left outer joins is that each record in the left table will be retrieved and where no associated right record exists then null values will be displayed for records in the right hand table but I've obviously misunderstood and would be grateful if someone could show me how to produce the required effect.
My scenario is pretty simple: 2 tables DiningTables and Reservations with columns as follows:
DiningTables: TBL_ID and TBL_Location - TBL_ID is the primary key
Reservationss::RES_TBL_ID and RES_Diner_Name - RES_TBL_ID is the primary key
There are 8 records in DiningTables and 4 records in Reservations and the objective is obtain the following output:
TBL_ID RES_Diner_Name
1 Jones
2 Smith
3 Bloggs
4 Mack
5 null
6 null
7 null
8 null
The SQL query I used is
SELECT Reservations.RES_Diner_Surname, DiningTables.TBL_ID FROM DiningTables LEFT OUTER JOIN Reservations ON DiningTables.TBL_ID = Reservations.RES_TBL_ID
That query generates 11 rows as follows:
TBL_ID RES_Diner_Name
1 Jones
1 Smith
1 Bloggs
1 Mack
2 null
3 null
4 null
5 null
6 null
7 null
8 null
I'm clealry missing something incredibly obvious and I kinda feel like the village idiot and would be extremely grateful for a clue!!
This may be a stupid question. But I just want to make sure i'm going in right direction. I wrote the following query. My purpose is to retrieve all the members who submitted loan applications. I just want to make sure my query is right? Can anyone veryfy this query? Do I have to use loanApplication table first instead of members. Also Do I need to use right outer joins instead of left outer joins?
Code Block SELECT Member.CUMemberId, LoanApplication.SubmittedOn, Member.LastName, Member.FirstName, Member.MiddleName, LoanApplication.Amount, LoanApplication.Decision, LoanApplication.Term, Rate.InterestRate, LoanApplication.Status, Member.CuStatus FROM Member INNER JOIN MemberLogon ON Member.Id = MemberLogon.MemberFK INNER JOIN LoanApplication ON Member.LastLoanApplicationFK = LoanApplication.Id AND Member.Id = LoanApplication.MemberFK LEFT OUTER JOIN Account ON Member.Id = Account.MemberFK AND LoanApplication.LoanFK = Account.Id LEFT OUTER JOIN Rate ON LoanApplication.RateFK = Rate.Id WHERE (LoanApplication.Status = 'Submitted')
