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
ADVERTISEMENT
May 1, 2004
Hi,
Iwant to make a left join between two tables connected with a specific field
but I also want to have some criteria on the right table.
I want to display all the rows from the first(left) table and only the rows from the secod (right) table that match the join and the criteria.
However If I have criteria on the second table the join behaves like an inner join displaying only the rows that exist in both tables.
In MS Access instead of the right table I use a query in which I enter the criteria and then I connect it with a left join in a new query in order to get the right results. How can I do itin MSSQLServer without using Views?
Must I use a subquery with Exists and what is the right syntax in order to achieve this?
Best Regards,
manolis Perrakis
View 1 Replies
View Related
Apr 24, 2008
Hi, I have a long sql query and I have to left join two tables. The problem is that I need to do a subquery in the left join for the second table. Let me give an example.
My second table haves this data:
ID GAME CREATED
1 SOCCER 1-8-1980
1 BASKKET 1-9-2000
2 TENIS 1-10-2000
2 GOLF 1-12-2000
2 CARS 1-9-2010
3 BIKE 1-2-1930
My first table left joins the second one by the ID column. So, I want first to determine the max record of each ID of the second table. Ones I have the max record for each ID in table one i want to left join it with table A.
How do I have to build the subquery in the inner join? I believe is something like this:
SELECT * FROM
TABLE1
LEFT OUTER JOIN TABLE2 (SUBQUERY) TABLE2_ALIAS
ON TABLE1.ID = TABLE2_ALIAS.ID
Thanks in advance!
View 7 Replies
View Related
Sep 13, 2006
Hello Chaps,
I have got question about the performance of subquery and left outer join
My scenario is that I have two tables Customer Mater and address book.
Customer Mater and address book tables hold the record for both carrier and customer. and i want retrieve the record for customer and related carrier as well
i have two options
1. either use left outer join (i.e. Join Customer master to Address Book 2 times, 1 for customer and 1 for carrier) by:
Address book (as customer) Left outer Join Customer Master (on related Key)
AddressBook (as carrier) Left Outer Join Customer master (on related Key)
2.or use 1 join for customer and use subquery to retrieve carrier name (from Customer table) as I only need that one
Address Book AB (as Customer) Join Customer Master CM
Subquery: Select carrier_name From customer_master cm2 where cm2.carrier = ab.carrier
can any one tell one which is the best as far as performance is concern. My opinion is that the subquery is better becuase left outer join will have the overhread of extra columns... not pretty sure about it..
Any idea?
regards,
Anas
View 9 Replies
View Related
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
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
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
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
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
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
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
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
View Related
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
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
May 14, 2008
Hi,
Whats the diference between a left join and a left outer Join
View 5 Replies
View Related
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
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
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
Feb 5, 2015
Why does this right join return the same results as using a left (or even a full join)?There are 470 records in Account, and there are 1611 records in Contact. But any join returns 793 records.
select Contact.firstname, Contact.lastname, Account.[Account Name]
from Contact
right join Account
on Contact.[Account Name] = Account.[Account Name]
where Contact.[Account Name] = Account.[Account Name]
View 3 Replies
View Related
Aug 17, 2007
Hi guys,
I'll appreciate any help with the following problem:
I need to retrieve data from 3 tables. 2 master tables and 1 transaction table.
1. Master table TBLOC contain 2 records :
rcd 1. S01
rcd 2. S02
2. Master table TBCODE contain 5 records:
rcd 1. C1
rcd 2. C2
rcd 3. C3
rcd 4. C4
rcd 5. C5
3. Transaction table TBITEM contain 4 records which link to 2 master table:
rcd 1. S01, C1, CAR
rcd 2. S01, C4, TOY
rcd 3. S01, C5, KEY
rcd 4. S02, C2, CAR
I use Left Join & Right Join to retrieve result below (using non-ASNI method) but it doesn't work.
Right Join method:
SELECT C.LOC, B.CODE, A.ITEM FROM TBITEM A RIGHT JOIN TBCODE B ON A.CODE = B.CODE
RIGHT JOIN TBLOC C ON A.LOC = C.LOC
GROUP BY C.LOC, B.CODE, A.ITEM ORDER BY C.LOC, B.CODE
When I use Non-ASNI method it work:
SELECT C.LOC, B.CODE, A.ITEM FROM TBITEM A, TBCODE B, TBLOC C
WHERE A.CODE =* B.CODE AND A.LOC =* C.LOC
GROUP BY C.LOC, B.CODE, A.ITEM ORDER BY C.LOC, B.CODE
Result:
LOC CODE ITEM
-----------------------------
S01 C1 NULL
S01 C2 NULL
S01 C3 CAR
S01 C4 TOY
S01 C5 KEY
S02 C1 NULL
S02 C2 CAR
S02 C3 NULL
S02 C4 NULL
S02 C5 NULL
Please Help.
Thanks.
View 3 Replies
View Related
Jan 25, 2015
-- Why is the left table in a LEFT JOIN limited by the where clause on the right table?eg
DECLARE @LeftTable TABLE (LeftID INT NOT NULL IDENTITY(1, 1), LeftValue INT NULL)
INSERT @LeftTable (LeftValue)
VALUES (111)
INSERT @LeftTable (LeftValue)
VALUES (222)
[code]....
View 2 Replies
View Related
Jan 26, 2007
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.
View 3 Replies
View Related
Jul 7, 2005
I need to make a left join from the freezefile f, to sped s, instead of having f.studentid = s.id in the where clause. Any help??
select f.studentid, f.studentname, f.sex, fs.mealstatus, s.except, s.lre, r.description, g.testid, g.scale_la, g.scale_ma, t.test_name, t.year
from freezefile f, fsapps fs, sped s, regtb_exception r, gqe_scores g, test_info t
where
f.type = 'ADM'
and
s.except = r.code
and
t.test_name = 'ISTEP'
and
t.year = 2004
and
g.testid = t.testid
and
f.studentid = fs.id
and
f.studentid = s.id
and
f.studentid = g.studentid
View 1 Replies
View Related
Aug 2, 2004
i'm shure it's some smal stiupid mistake bat I can't find it, PLZ help.
1)
select komorka from #plantemp
--result
komorka
09
10
I-P
II-P
III-P
SI/1
SI/2
SI/3
2)
select komorka,ustalenia from analiza_1 a where a.koniec between '20040701'and '20040731'
komorka ustalenia
SI/1788138.9300
SI/246638.4900
SI/216218.4000
08.0000
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
View 3 Replies
View Related
Jan 9, 2007
There are two tables:
tblIndices:
IndexID, Name
1index1
2index2
3index3
.
.
.
tblBasketConstituents
ID, ParentIndexID, ChildIndexID, Weight
121 20
223 80
313 50
412 50
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]
View 1 Replies
View Related
Feb 26, 2004
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.
Lito
View 6 Replies
View Related
Mar 6, 2007
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.
thanks
-Ron-
View 5 Replies
View Related
Dec 2, 2007
Hi Guys,
I started working with linq and vb9.0 but i have a small problem i could feagure how to solve in c# but not in vb
I wanted to make left join or right join on vb 9.0 and linq is it possible or this is only c# feature ?
Waiting to hear from u guys,
Thanks
Softy
View 2 Replies
View Related
Jan 7, 2002
Hello!
I need to write a query using left outer join and I'm having trouble with it.
I have 2 tables:customer and cust_info.
I want to pull all records from customer table and cust_info table even if there is no related data in cust_info table.
But I need one condition in this query:only records from customer table where cus_type in ("A","B","C").
I don't need all other types ("D","E").
So my query looks like this:
select customer.cus_name,customer.cus_address,customer.cu s_type,cus_info.status
from customer
left outer join cus_info ON customer.sxdat_pk = cus_info.sxdat_pk
and cus_type in ("A","B","C")
AND cus_info.cus_table = 'CUSTOMER'
The result should be like this:
cus_name cus_address cus_type status
Amoco 457 bent A new
Bingo 47 lone oak C NULL
Sears 1020 Magic dr. B exist
But my query pulls records for customers with type "D" and "E" that I'm trying to exclude from result.
Please help.
Thanks,
lena
View 4 Replies
View Related
Apr 18, 2008
Hello everybody!!!
I have to left join 2 tables. The first consists of columns:id, description, descr_num.
The second table: id, descr_num, value.
I need to extract description from table1 where descr_num is in the range , say, 1-10.
LEFT JOIN
extract value from table2 ,descr_num should be in the same range. in table2 some values from (1,10) and desc_num could be not present.
BUT i want this left join to be limited as i said.
Is there any solution to this prob. without creating temp tables. Or actually can i do such a join?
Thank you
View 2 Replies
View Related
Jul 18, 2006
Now I have a table with the table design as following :
table cst_EmpProfile
intEmpId nvcEmpName nvcEmpAddress intEmployeeType bitActiv
1 Peter Null Null true
2 Juli 1, xxxx 2 true
3 Sam Null Null False
table cst_EmpType
intEmpType nvcEmployeeType
1 Free Enginner
2 Manager
3 Operator
To join the table but MUST follow the condition as bitActiv = TRUE:
select emp.nvcEmpName, emp.nvcEmpAddress, ety. nvcEmployeeType from cst_EmpProfile emp left join cst_EmpType on emp.intEmployeeTypee = ety.intEmpType and emp.bitActiv = 1.
But, the sql statement doesnt output the my expected result.
Because the data row return must be 1st and 2nd row as it bitActiv = true.
So, how's I going achieve what i want. tq.
View 1 Replies
View Related
Jan 10, 2007
Hi, I'm getting the error Syntax error (missing operator) in query expression 'dbPWork.id = dbPWorkWord.work_id LEFT JOIN words ON dbPWorkWord.word_id = words.id'." on the query below. Everything looks in place to me. Do you see anything that shouldn't work?
Query:
Code:
Code:
SELECT DISTINCT dbPWork.id, description_e as description, w_id , dateStart, dateEnd FROM dbPWork
LEFT JOIN dbPWorkW ON dbWork.id = dbPWorkW.work_id
LEFT JOIN words ON dbPWorkW.word_id = words.id
Everything works fine until I add the second LEFT JOIN statement. Any ideas? I'm stumped.
View 4 Replies
View Related
Sep 22, 2005
I have a left join that doesn't suit my request.
select * from A
left join B
on A.x=B.x
The problem is that for a specific record in A we can find 1 or more entries in B. What I want is to return only the 1st entry found on B.
thx
View 3 Replies
View Related
