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,

code]...

View 6 Replies


ADVERTISEMENT

Transact SQL :: How To Select A Single Record In A Left Join

Sep 16, 2015

My tables look like this:

 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.

View 10 Replies View Related

Select Command - Left Join Versus Inner Join

Aug 9, 2013

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?

View 4 Replies View Related

Select DISTINCT In LEFT JOIN

Feb 14, 2008

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 

View 24 Replies View Related

Double SELECT To (LEFT) JOIN

Jul 26, 2013

I am currently working with 2 tables:

tbl_users: UserID,Username,ClientFK
tbl_clients: ClientID, ClientName

Now I want to get all Users with same Client but my parameter of my stored procedure is @Username.

This code works fine:

SELECT
UserID,
ClientFK,
WebLogin,
WebPassword,
WindowsUsername,
BasePriority,
IsAdmin,
DateCreated,
Enabled
FROM tbl_User
WHERE ClientFK = (SELECT [ClientFK] FROM tbl_User WHERE [WindowsUsername] = 'Livermorium')

But i don't want to have 2 selects and prefer a left join.

Is it possible to write a better select statement?

View 10 Replies View Related

Sub-select Vs. LEFT OUTER JOIN

May 11, 2007

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

Which is better and why?

View 2 Replies View Related

Transact SQL :: How To Select A Number Less Or Equal Than A Supplied Number

Jun 23, 2015

Got this query and I need the following result;

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

View 4 Replies View Related

Transact SQL :: How To Number Rows In SELECT

Oct 8, 2015

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

View 2 Replies View Related

Transact SQL :: How To Select Number For Varchar Column

Sep 15, 2015

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)

View 5 Replies View Related

Transact SQL :: Difference Between Inner Join And Left Outer Join In Multi-table Joins?

Oct 8, 2015

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.

View 5 Replies View Related

Transact SQL :: Using WITH Statement Rather Than Putting A SELECT In A JOIN?

Jun 18, 2015

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?

View 3 Replies View Related

Transact SQL :: Select All Days In Week Number X Including Last Year If Necessary

May 24, 2015

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

[Code] ....

View 2 Replies View Related

Transact SQL :: Select All The Days In Week Number X Including Last Year If Necessary

May 24, 2015

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

[Code] ...

View 2 Replies View Related

Transact SQL :: Insert Using Select Intersect / Except - Number Of Supplied Values Not Match Table Definition

May 12, 2015

I have two tables, D and C, whose INTERSECT / EXCEPT values I'd like to insert in the following table I've created

CREATE TABLE DinCMatch (
servername varchar(50),
account_name varchar (50),
date_checked datetime DEFAULT getdate()
)

The following stmt won't work...

INSERT INTO DinCMatch
select servername, account_name from D
intersect
select servername, account_name from C

... as the number of supplied values does not match table definition.

View 2 Replies View Related

Transact SQL :: Left Join With Subquery

Aug 24, 2015

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 am trying to left join another table but  I got

Incorrect syntax near the keyword 'LEFT'.

View 9 Replies View Related

Transact SQL :: Left Join Returns Too Many Rows Vertically

Jun 18, 2015

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.

View 5 Replies View Related

Left Join Vs Left Outer Join Syntax Generates Different Execution Plans

Apr 16, 2008



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

View 5 Replies View Related

Transact SQL :: Left Join To Get Info From A Table With Foreign Keys

Sep 13, 2015

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

View 6 Replies View Related

Transact SQL :: Left Outer Join And Transaction Isolation Level

Nov 30, 2015

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?

View 3 Replies View Related

Transact SQL :: Pass Results From Select To Another Select

Oct 13, 2015

I've got a select as follows:

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...

View 16 Replies View Related

Select Where Left In Left

Dec 20, 2006

hello

for MS SQL 2000, i cannot get it

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


thank you for helping

View 4 Replies View Related

Transact SQL :: How To Get First Table All Rows In Left Join If Condition Is Applied On Second Table

Aug 15, 2015

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

[code] ....

View 9 Replies View Related

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

where AL013..gl00105.actnumst is null

View 1 Replies View Related

How To Select A Word Substring In A Coumn Header From Right To Left

Oct 10, 2007

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!????

View 1 Replies View Related

How To Return Partial String Using CharIndex And Left In Same Select

Jun 22, 2015

Select
left(
[Description],(charindex(';',[Description],1)-1))
from xxxx

Example of Description contains

Ankle Supports; Color=Black; Size=S

So I want the left side up to and NOT including the semi colon.

View 14 Replies View Related

Transact SQL :: Left Pad With 0 And Extract 2 Characters On The Left?

Oct 28, 2015

I have a table PLACE with a character column CODE

[Code] [nchar](4) 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?

View 10 Replies View Related

Left Join Vs Left Outer Join

Apr 7, 2008

Is there any difference between left join and left outer join in sql server 2000?please reply with example if any?
Thanks in advance

View 13 Replies View Related

Left Join Vs Left Outer Join

May 14, 2008

Hi,

Whats the diference between a left join and a left outer Join

View 5 Replies View Related

Integration Services :: How To Perform Left Restricted Join In Merge Join Transformation

May 22, 2015

I have two xml source and i need only left restricted data.

how can i perform left restricted join?

View 2 Replies View Related

The Row Number In A SELECT

Jan 21, 2004

How do I get the row number in a SELECT

Select ???? as Row, Name, Stars From Member


Row Name Stars
---------------------------------------
1 Enigma ****
2 BreittKaiser *****
3 BlindMan *****

View 13 Replies View Related

Select Where Number Is Never 1 Or 2

Oct 18, 2013

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;

+------+------+------+
| A_ID | B_ID | NUM |
+------+------+------+
| 101 | 101 | 1 |
| 101 | 102 | 2 |
| 101 | 201 | 3 |
| 101 | 301 | 4 |
| 102 | 201 | 2 |
| 103 | 102 | 3 |
| 201 | 101 | 1 |
| 301 | 301 | 2 |
| 401 | 102 | 7 |
| 501 | 102 | 1 |
| 501 | 301 | 3 |
+------+------+------+

11 rows in set (0.00 sec)

View 6 Replies View Related

Select By Row Number?

Jun 1, 2006

Hello all, I am using SQL 2000 SP4.





I have a table with a studentID column. Is there any way to select a single


row of data from that column by referencing the row number?





ex. select studentID from Table where row = 2





The above example obviously does not work, but is there a way to do


something similar? I want to go down the table sequentially and knowing any of the data in the other columns does not help me with that at all.




Any help is appreciated, thanks

View 1 Replies View Related

'Left Outer Merge Join' Failing To Join Valid Row

Aug 10, 2007

Scenario:

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?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved