Transact SQL :: Select From A Select Using Row Number With Left Join
Aug 20, 2015
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,
 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.
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?
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
declare @NumberToCompareTo int set @NumberToCompareTo = 8 declare @table table ( number int ) insert into @table select 4
[Code] ....
The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5
I have a SELECT statement with multiple JOINs. I want to number the rows returned by this SELECT statement. What's the easiest way to do this?
My current SELECT statement returns this:
ProjectId -- TaskId -- TaskName 123 Â - 111 -- Do something 123 Â - 222 -- Do something else 123 Â - 333 -- Do one more thing
I want to return this:
ProjectId -- TaskId -- TaskName -- Sequence 123  - 111 -- Do something  -- 1 123  - 222 -- Do something else  -- 2 123  - 333 -- Do one more thing  -- 3
I have a table with number and varchar columns. The last insert statement has 1 inserted.
The  select statement should retrieve   Â
a         b                                                          1         1
CREATE TABLE [dbo].[test1]( Â [a] [int] NULL, Â [b] [varchar](10) NULL ) ON [PRIMARY] Â insert into test1 values (1,'a') Â insert into test1 values (2,'b') Â insert into test1 values (4,'d') Â insert into test1 values (12,'x') Â insert into test1 values (15,NULL) Â insert into test1 values (1,1)
I was writing a query using both left outer join and inner join. And the query was ....
SELECT Â Â Â Â Â Â Â S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname FROM Â Â Â Â Â Â Â Production.Suppliers AS S LEFT OUTER JOIN Â Â Â Â Â Â (Production.Products AS P Â Â Â Â Â Â Â Â INNER JOIN Production.Categories AS C
[code]....
However ,the result that i got was correct.But when i did the same query using the left outer join in both the cases
i.e..
SELECT Â Â Â Â Â Â Â S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname FROM Â Â Â Â Â Â Â Production.Suppliers AS S LEFT OUTER JOIN (Production.Products AS P LEFT OUTER JOIN Production.Categories AS C ON C.categoryid = P.categoryid) ON S.supplierid = P.supplierid WHERE S.country = N'Japan';
The result i got was same,i.e
supplier   country   productid   productname   unitprice   categorynameSupplier QOVFD   Japan   9   Product AOZBW   97.00   Meat/PoultrySupplier QOVFD   Japan  10   Product YHXGE   31.00   SeafoodSupplier QOVFD   Japan  74   Product BKAZJ   10.00   ProduceSupplier QWUSF   Japan   13   Product POXFU   6.00   SeafoodSupplier QWUSF   Japan   14   Product PWCJB   23.25   ProduceSupplier QWUSF   Japan   15   Product KSZOI   15.50   CondimentsSupplier XYZ   Japan   NULL   NULL   NULL   NULLSupplier XYZ   Japan   NULL   NULL   NULL   NULL
and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.
Is there any reason to use the WITH statement rather than putting a SELECT in a JOIN? Does one method perform better or is it just a matter of preference?
SQL express 2012. I am trying to case in the where part and having a syntax errors -Â This is what i am trying to do:
select all the days in week number x including last year if necessary... so if the year start not at the beginning of the week then look in last year as well ( for the same week number of this year and last week nu of last year)
declare @yyyy int = 2014,-- THE YEAR @mm int = 1,-- THE MONTH @week1No int = 1,-- THE WEEK NUMBER IN THE YEAR @week2No int = 37-- THE last WEEK NUMBER IN last YEAR select count(tblDay.start)-- tblDay.start IS smallDatetime
I am trying to case in the where part and having a syntax errors -Â this is what i am trying to do:
Select all the days in week number x including last year if necessary... so if the year start not at the beginning of the week then look in last year as well ( for the same week number of this year and last week nu of last year)
declare @yyyy int = 2014,-- THE YEAR @mm int = 1,-- THE MONTH @week1No int = 1,-- THE WEEK NUMBER IN THE YEAR @week2No int = 37-- THE last WEEK NUMBER IN last YEAR select count(tblDay.start)-- tblDay.start IS smallDatetime
SELECT A.EmpId,A.IncidentDate FROM EmployeePoints1 as A WHERE IncidentDate= (SELECT MAX(IncidentDate) FROM EmployeePoints1 WHERE EmpId = A.EmpId) AND (DATEADD(day,28,DATEADD(WEEK, DATEDIFF(WEEK, 0,A.IncidentDate), 0)) < DATEADD(WEEK, DATEDIFF(WEEK, 0,GetDate()), 0)) AND (A.IncidentCode = 'I' OR A.IncidentCode = 'A') LEFT JOIN EmployeeTotalPoints1 ON EmployeeTotalPoints1.EmpId = A.EmpId
I have a query that based 2 tables. I wrote a query with a left join on the base table but the result set returns multiple rows for each occurrence in the second table because of the left join. I want but to return all records from on table A and only matching records from table B which id but I would wan tit to keep return them vertically as the because it make it difficult to read when put in a spreadsheet. It want it to return the values horizontally so the rows are not increasing for each occurrence on table b.
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".
I am still new to SQL and I am having trouble obtaining the results I need from a query. I have worked on this command for some time and searched the internet but cannot seem to still get it correct.
I have a table called Patient. It's primary key is pat_id.
I have a second table called Coverage. It has no primary key. The foreign keys are pat_id, coverage_plan_id, and hosp_status.
I have a third table called Coverage_History.  It has a primary key consisting of pat_id, hosp_status, copay_priority, and effective_from.Â
I want to get the pat_id and all the coverage information that is current.  The coverage table contains specific insurance policy information.  The coverage_history table will indicate the effective dates for the coverage. So the tables could contain something like this:
Patient (pat_id and lname) P123Â Monto P124Â Minto P125Â Dento P126Â Donto
Coverage (pat_id, coverage_plan_id, hosp_status, policy_num) P123Â Â Â Â MED1Â Â OPÂ Â A1499 P123Â Â Â Â ACT4Â Â OPÂ Â H39BÂ P124Â Â Â Â MED1Â Â OPÂ Â C90009 P124Â Â Â Â RACÂ Â Â OPÂ Â 99KKKK P124Â Â Â Â RACÂ Â Â OPÂ Â 99KKKK P124Â Â Â Â MED1Â Â OPÂ Â C90009 P125Â Â Â Â ARPÂ Â Â OPÂ Â G190 P126Â Â Â Â BCBÂ Â Â OPÂ Â H88
Coverage_History (pat_id, hosp_status, copay_priority, effective_from, coverage_plan_id, effective_to) P123Â Â OPÂ Â 1Â Â 20150102Â MED1Â Â Â NULL P123Â Â OPÂ Â 2Â Â 20150102Â ACT4Â Â Â NULL P124Â Â OPÂ Â 1Â Â 20150203Â RACÂ Â Â Â 20150430 P124Â Â OPÂ Â 2Â Â 20150203Â MED1Â Â Â 20150430 P124Â Â OPÂ Â 1Â Â 20150501Â MED1Â Â Â NULL P124Â Â OPÂ Â 2Â Â 20150501Â RACÂ Â Â Â NULL P125Â Â OPÂ Â 1Â Â 20150801Â ARPÂ Â Â Â NULLÂ P126Â Â OPÂ Â 1Â Â 20150801Â BCBÂ Â Â Â 20160101
select p.pat_id, p.lname, ch.coverage_plan_id, ch.hosp_status, ch.effective_from, ch.effective_to, ch.copay_priority,       from patient p       left join   ( coverage_history ch left join coverage c on ch.coverage_plan_id = c.coverage_plan_id and ch.patient_id = c.patient_id and                                   (ch.effective_to is NULL or ch.effective_to >= getdate() )         ) on ch.patient_id = p.patient_id            where ( ch.effective_to is NULL or ch.effective_to >= getdate() )    So I want to see:
P123 Monto MED1 OP  20150102   NULL      1 P123 Monto ACT4 OP  20150102   NULL      2 P124 Minto MED1 OP  20150501   NULL      1 P124 Minto RAC  OP  20150501   NULL      2 P125 Dento ARP  OP  20150801   NULL      1 P126 Donto BCB  OP  20150801   20160101   1
We have a service that inserts some rows into a parent table (P) and child table (C). This operation is atomic and performed within a transaction.
We also have a service that queries these tables such that rows are (should only be) returned from P where there are no children for that parent.
The SQL that performs this is simplified below:
SELECT P.SomeCol FROM P LEFT OUTER JOIN C ON P.PKofP_Value = C.PkofP_Value WHERE C.PkofPValue IS NULL AND P.SomeOtherCol=0
Our expectation is that the query service should only return rows from P where there are no rows in C.
However, this seems not to be the case, and occasionally we find that rows from P are returned where there are matching rows in C.
We are sure that the process that inserts rows into P and C does so within a single transaction.
We have traced this with SQLTrace and can see the txn stag and committing and all operations using the same transactionid within the transaction.
We are running the default isolation level committed.
In SQLTrace we can see the query process start, the inserter process start and complete and then the query process continue (after presumably being blocked).
So how can the query process "miss" the child rows and return the parent from the above query?
Is it possible that, in this isolation level, the inserter process can block the query process such that when the inserter process commits and when the query process continues it does not see the child rows inserted because they were inserted in the table/index "behind" where the query process has already read - some kind of phantom phenomenon?
select computer, count(*) as MissedCount from WInUpdates_Neededreq WHERE LoggedDate BETWEEN DATEADD (DAY, - 5, GETDATE()) AND GETDATE() and LastReportTime !< DATEADD (DAY, -5, GETDATE()) group by computer
I need to make a join onto another table but don't want to lose the coutn(*) as MissedCount.
How can I join to another table and still keep the count form the original table. I want ot join to tblogons.workstationname and return computer from the original query...
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
I am using stored procedure to load gridview but problem is that i am not getting all rows from first table[Â Subject] on applying conditions on second table[ Faculty_Subject table] ,as you can see below if i apply condition :-
Faculty_Subject.Class_Id=@Class_Id
Then i don't get all subjects from subject table, how this can be achieved.
Sql Code:- GO ALTER Proc [dbo].[SP_Get_Subjects_Faculty_Details] @Class_Id int AS BEGIN
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
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!????
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?
I have a problem where I essentially want to find all the B_ID numbers that do not have any ones or twos next to them in the whole table (see table below).
I come up with this query, however it is wrong, because certain B_ID do not have a 1 or 2 on some lines, but then they do on others :(.
SELECT B_ID FROM NUMBER WHERE NUM != 1 AND NUM != 2; SELECT * FROM NUMBER;
OLEDB source 1 SELECT ... ,[MANUAL DCD ID] <-- this column set to sort order = 1 ... FROM [dbo].[XLSDCI] ORDER BY [MANUAL DCD ID] ASC
OLEDB source 2 SELECT ... ,[Bo Tkt Num] <-- this column set to sort order = 1 ... FROM ....[dbo].[FFFenics] ORDER BY [Bo Tkt Num] ASC
These two tasks are followed immediately by a MERGE JOIN
All columns in source1 are ticked, all column in source2 are ticked, join key is shown above. join type is left outer join (source 1 -> source 2)
result of source1 (..dcd column) ... 4-400-8000119 4-400-8000120 4-400-8000121 4-400-8000122 <--row not joining 4-400-8000123 4-400-8000124 ...
result of source2 (..tkt num column) ... 4-400-1000118 4-400-1000119 4-400-1000120 4-400-1000121 4-400-1000122 <--row not joining 4-400-1000123 4-400-1000124 4-400-1000125 ...
All other rows are joining as expected. Why is it failing for this one row?
