Left Join Help Needed!

Dec 27, 2007

I’m attempting to use a Left Join to pull information from a table that contains my objectives and place in with information from a Summary table. I use one where clause per Left Join, that defines which rows from the objective table I want that left join to represent (for instance “(mdt.SLA_NAME = 'Duration MNOC to NDC % Met SLA' OR mdt.SLA_NAME IS NULL )� ). I have 7 left joins and where statements.

The problem I’m having is that the where clause are removing all rows where the Left Join does not result in a match, despite the fact that I have the OR IS NULL in the where clause.

Can anyone help!

The full SQL is as follows:


SELECT DATE_DAY
, SEVERITY
, ENTRY_TYPE
, EQ_REGION
, EQ_MARKET_CLUSTER
, FORCE_STATUS
, NUM_CREATED
, NUM_OPEN
, NUM_AWAITING_DISPATCH
, NUM_OPEN_IN_DUE_DATE
, NUM_DISPATCH_TO
, DURATION_DISPATCH_TO
, SLA_DURATION_DISPATCH_TO
, SLA_DURATION_DISPATCH_TO_G_L
, MET_SLA_DISPATCH_TO
, mdt.SLA AS SLA_MET_DISPATCH_TO
, mdt.SLA_G_L AS SLA_MET_DISPATCH_TO_G_L
, C_NUM_DISPATCH_TO
, C_DURATION_DISPATCH_TO
, C_SLA_DURATION_DISPATCH_TO
, C_SLA_DURATION_DISPATCH_TO_G_L
, C_MET_SLA_DISPATCH_TO
, mdt.SLA AS C_SLA_MET_DISPATCH_TO
, mdt.SLA_G_L AS C_SLA_MET_DISPATCH_TO_G_L
, NUM_DISPATCH_FROM
, DURATION_CREATE_TO_DISPATCH_FROM
, NUM_DISPATCH_TO_TO_FROM
, DURATION_DISPATCH_FROM
, SLA_DURATION_DISPATCH_FROM
, SLA_DURATION_DISPATCH_FROM_G_L
, MET_SLA_DISPATCH_FROM
, mdf.SLA AS SLA_MET_DISPATCH_FROM
, mdf.SLA_G_L AS SLA_MET_DISPATCH_FROM_G_L
, C_NUM_DISPATCH_FROM
, C_DURATION_CREATE_TO_DISPATCH_FROM
, C_NUM_DISPATCH_TO_TO_FROM
, C_DURATION_DISPATCH_FROM
, C_SLA_DURATION_DISPATCH_FROM
, C_SLA_DURATION_DISPATCH_FROM_G_L
, C_MET_SLA_DISPATCH_FROM
, mdf.SLA AS C_SLA_MET_DISPATCH_FROM
, mdf.SLA_G_L AS C_SLA_MET_DISPATCH_FROM_G_L
, NUM_CLOSED
, DURATION_WIP_TO_RESOLVE
, SLA_WIP_TO_RESOLVE
, SLA_WIP_TO_RESOLVE_G_L
, MET_SLA_WIP_TO_RESOLVE
, wip.SLA AS SLA_MET_WIP_TO_RESOLVE
, wip.SLA_G_L AS SLA_MET_WIP_TO_RESOLVE_G_L
, DURATION_MTTR
, SLA_MTTR
, SLA_MTTR_G_L
, MET_SLA_MTTR
, mttr.SLA AS SLA_MET_MTTR
, mttr.SLA_G_L AS SLA_MET_MTTR_G_L
, NUM_RESOLVE_FD
, rfd.SLA AS SLA_RESOLVE_FD
, rfd.SLA_G_L AS SLA_RESOLVE_FD_G_L
, NUM_DISPATCH_NTF
, ntf.SLA AS SLA_DISPATCH_NTF
, ntf.SLA_G_L AS SLA_DISPATCH_NTF
, AR_NUM_MET_DUE_DATE
, ar.SLA AS AR_SLA_MET_DUE_DATE
, ar.SLA_G_L AS AR_SLA_MET_DUE_DATE
FROM (
SELECT DATE_DAY
, CASE
WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN
(CASE WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END)
WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN
(CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END)
ELSE SEVERITY END AS SEVERITY
, ENTRY_TYPE
, EQ_REGION
, EQ_MARKET_CLUSTER
, CASE WHEN FORCE_STATUS = 'Jeopardy' OR FORCE_STATUS IS NULL THEN FORCE_STATUS ELSE 'Other' END AS FORCE_STATUS
, sum( NUM_CREATED ) AS NUM_CREATED
, sum( NUM_OPEN ) AS NUM_OPEN
, sum( NUM_AWAITING_DISPATCH ) AS NUM_AWAITING_DISPATCH
, sum( NUM_OPEN_IN_DUE_DATE ) AS NUM_OPEN_IN_DUE_DATE
, sum( NUM_DISPATCH_TO ) AS NUM_DISPATCH_TO
, sum( DURATION_DISPATCH_TO ) AS DURATION_DISPATCH_TO
, max( SLA_DURATION_DISPATCH_TO ) AS SLA_DURATION_DISPATCH_TO
, max( SLA_DURATION_DISPATCH_TO_G_L ) AS SLA_DURATION_DISPATCH_TO_G_L
, sum( MET_SLA_DISPATCH_TO ) AS MET_SLA_DISPATCH_TO
, sum( C_NUM_DISPATCH_TO ) AS C_NUM_DISPATCH_TO
, sum( C_DURATION_DISPATCH_TO ) AS C_DURATION_DISPATCH_TO
, max( C_SLA_DURATION_DISPATCH_TO ) AS C_SLA_DURATION_DISPATCH_TO
, max( C_SLA_DURATION_DISPATCH_TO_G_L ) AS C_SLA_DURATION_DISPATCH_TO_G_L
, sum( C_MET_SLA_DISPATCH_TO ) AS C_MET_SLA_DISPATCH_TO
, sum( NUM_DISPATCH_FROM ) AS NUM_DISPATCH_FROM
, sum( DURATION_CREATE_TO_DISPATCH_FROM ) AS DURATION_CREATE_TO_DISPATCH_FROM
, sum( NUM_DISPATCH_TO_TO_FROM ) AS NUM_DISPATCH_TO_TO_FROM
, sum( DURATION_DISPATCH_FROM ) AS DURATION_DISPATCH_FROM
, max( SLA_DURATION_DISPATCH_FROM ) AS SLA_DURATION_DISPATCH_FROM
, max( SLA_DURATION_DISPATCH_FROM_G_L ) AS SLA_DURATION_DISPATCH_FROM_G_L
, sum( MET_SLA_DISPATCH_FROM ) AS MET_SLA_DISPATCH_FROM
, sum( C_NUM_DISPATCH_FROM ) AS C_NUM_DISPATCH_FROM
, sum( C_DURATION_CREATE_TO_DISPATCH_FROM ) AS C_DURATION_CREATE_TO_DISPATCH_FROM
, sum( C_NUM_DISPATCH_TO_TO_FROM ) AS C_NUM_DISPATCH_TO_TO_FROM
, sum( C_DURATION_DISPATCH_FROM ) AS C_DURATION_DISPATCH_FROM
, max( C_SLA_DURATION_DISPATCH_FROM ) AS C_SLA_DURATION_DISPATCH_FROM
, max( C_SLA_DURATION_DISPATCH_FROM_G_L ) AS C_SLA_DURATION_DISPATCH_FROM_G_L
, sum( C_MET_SLA_DISPATCH_FROM ) AS C_MET_SLA_DISPATCH_FROM
, sum( NUM_CLOSED ) AS NUM_CLOSED
, sum( DURATION_WIP_TO_RESOLVE ) AS DURATION_WIP_TO_RESOLVE
, max( SLA_WIP_TO_RESOLVE ) AS SLA_WIP_TO_RESOLVE
, max( SLA_WIP_TO_RESOLVE_G_L ) AS SLA_WIP_TO_RESOLVE_G_L
, sum( MET_SLA_WIP_TO_RESOLVE ) AS MET_SLA_WIP_TO_RESOLVE
, sum( DURATION_MTTR ) AS DURATION_MTTR
, max( SLA_MTTR ) AS SLA_MTTR
, max( SLA_MTTR_G_L ) AS SLA_MTTR_G_L
, sum( MET_SLA_MTTR ) AS MET_SLA_MTTR
, sum( NUM_RESOLVE_FD ) AS NUM_RESOLVE_FD
, sum( NUM_DISPATCH_NTF ) AS NUM_DISPATCH_NTF
, sum( AR_NUM_MET_DUE_DATE ) AS AR_NUM_MET_DUE_DATE
FROM CTS.dbo.NDC_Scorecard_Summary_Table t
WHERE DATE_DAY >= cast(convert(varchar(8),getdate()-30,1) as datetime)
AND DATE_DAY < cast(convert(varchar(8),getdate(),1) as datetime)
AND upper(EQ_REGION) IN ( 'NSD', 'NORTHEAST', 'CENTRAL', 'SOUTHEAST', 'WEST' )
AND upper(EQ_MARKET_CLUSTER) IN ( 'NEW ENGLAND','GEORGIA' )
GROUP BY DATE_DAY
, CASE
WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN
(CASE WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END)
WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN
(CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END)
ELSE SEVERITY END-- AS SEVERITY
, ENTRY_TYPE
, EQ_REGION
, EQ_MARKET_CLUSTER
, CASE WHEN FORCE_STATUS = 'Jeopardy' OR FORCE_STATUS IS NULL THEN FORCE_STATUS ELSE 'Other' END-- AS FORCE_STATUS
) t
LEFT JOIN CTS.dbo.NDC_SLAs mdt ON t.SEVERITY=mdt.SLA_SEVERITY
LEFT JOIN CTS.dbo.NDC_SLAs mdf ON t.SEVERITY=mdf.SLA_SEVERITY
LEFT JOIN CTS.dbo.NDC_SLAs wip ON t.SEVERITY=wip.SLA_SEVERITY
LEFT JOIN CTS.dbo.NDC_SLAs mttr ON t.SEVERITY=mttr.SLA_SEVERITY
LEFT JOIN CTS.dbo.NDC_SLAs rfd ON t.SEVERITY=rfd.SLA_SEVERITY
LEFT JOIN CTS.dbo.NDC_SLAs ntf ON t.SEVERITY=ntf.SLA_SEVERITY
LEFT JOIN CTS.dbo.NDC_SLAs ar ON t.SEVERITY=ar.SLA_SEVERITY
WHERE (mdt.SLA_NAME = 'Duration MNOC to NDC % Met SLA' OR mdt.SLA_NAME IS NULL )
AND (mdf.SLA_NAME = 'Duration NDC to Field % Met SLA' OR mdf.SLA_NAME IS NULL )
AND (wip.SLA_NAME = 'Duration Dispatch Out % Met SLA' OR wip.SLA_NAME IS NULL )
AND (mttr.SLA_NAME = 'Total MTTR % Met SLA' OR mttr.SLA_NAME IS NULL )
AND (rfd.SLA_NAME = '% Resolved 1st Dispatch' OR rfd.SLA_NAME IS NULL )
AND (ntf.SLA_NAME = '% Dispatched NTF' OR ntf.SLA_NAME IS NULL )
AND (ar.SLA_NAME = '% Met Due Date' OR ar.SLA_NAME IS NULL )

View 1 Replies


ADVERTISEMENT

Problem With SQL Server Left Outer Join: Help Needed

Nov 1, 2004

I am getting different results with LEFT outer join operator and *= operator. With *= I am getting the expected results. Can anyone look at SQL and tell what I am doing wrong?

SQL with Left Outer join operator:

select CurrentWeekFinMetrics.[Hub+],
WeeklyMetricsFormat.line#,
WeeklyMetricsFormat.MetricsType,
WeeklyMetricsFormat.Metrics,
WeeklyMetricsFormat.Measure, WeeklyMetricsFormat.jobs, case when dataformatchar is not null then
case when IsPrefix = 'Y' then dataformatchar + convert (varchar, CurrentWeekFinMetrics.displayCol )
else convert (varchar, CurrentWeekFinMetrics.displayCol ) + dataformatchar end
else
convert (varchar, CurrentWeekFinMetrics.displayCol )
end
from WeeklyMetricsFormat LEFT JOIN CurrentWeekFinMetrics on (WeeklyMetricsFormat.Line# = CurrentWeekFinMetrics.Line#)
where CurrentWeekFinMetrics.WeekEndingDate = '10/09/04' and CurrentWeekFinMetrics.[Hub+] = 'Amstelveen'
order by CurrentWeekFinMetrics.[Hub+], WeeklyMetricsFormat.Line#


SQL with *= operator
select CurrentWeekFinMetrics.[Hub+],
WeeklyMetricsFormat.line#,
WeeklyMetricsFormat.MetricsType,
WeeklyMetricsFormat.Metrics,
WeeklyMetricsFormat.Measure, WeeklyMetricsFormat.jobs, case when dataformatchar is not null then
case when IsPrefix = 'Y' then dataformatchar + convert (varchar, CurrentWeekFinMetrics.displayCol )
else convert (varchar, CurrentWeekFinMetrics.displayCol ) + dataformatchar end
else
convert (varchar, CurrentWeekFinMetrics.displayCol )
end
from WeeklyMetricsFormat , CurrentWeekFinMetrics
where CurrentWeekFinMetrics.WeekEndingDate = '10/09/04' and CurrentWeekFinMetrics.[Hub+] = 'Amstelveen'
AND (WeeklyMetricsFormat.Line# *= CurrentWeekFinMetrics.Line#)

For Left outer join operator, I am getting 54 rows, *= I am getting 69 rows.

Thanks and Regards
mb

View 2 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

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

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

'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

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

Right Join Returns Same Results As Left Join

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

How To Join 3 Tables Using Left Or Right Join Keyword?

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

Why Is Left Table In LEFT JOIN Limited By Where Clause On Right Table

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

SQL LEFT JOIN Help

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

Left Join Help

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

Left/right Join

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

Left Join

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

How Many 'LEFT JOIN' Is Too Many?

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

Left Join

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

Left Join Using Vb 9.0 And LINQ

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

Left Outer Join-please Help!

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

LEFT JOIN Problem

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

Left Join Table

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

Stumped - LEFT JOIN

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

Left Join Question

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

Difference Between LEFT N RIGHT JOIN

Dec 6, 2005

Hi,
I have got a query for you guys. Can any one temme the difference between

"FROM Table_A LEFT JOIN Table_B" and "FROM Table_B RIGHT JOIN Table_A"



;)
Thanks,
Rahul Jha

View 2 Replies View Related

Left Join And Subquery

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

Left Outer Join?

Jun 17, 2008

Hi there,

I have three tables
Job
JobID
WeightIn
WeightOut

Operator
OperatorID
NameFirst
NameLast

JobOperator
JobOperatorID
JobID
OperatorID

a job can be done with 1 or more operators
I want to be able to show user both assigned and unassigned operator on the front end on a certain job so I haev only parm being passed in @jobID.

How do I got about showing all the operators but somehow differentiate ones already assigned to job

Thanks!

View 5 Replies View Related

Duplicate When Do Left Join

Jan 27, 2014

select a.[Cuno], [doc_type], [Doc_name]
from [assembly] a
left join [assembly_Doc] doc
ON a.id = doc.assembly_id
left join [assembly_Recog] re
ON a.id = re.assembly_id
where [activate] =1
and doc.doc_type = 1

I only have two records for doc_type =1 in the [assembly_Doc] table. Without joining the third table [assembly_Recog], the results is corrected, 2 records returned. I need to join with [assembly_Recog] table, but when it joined, I got the duplicate records. It listed twice. pls see the results below:

CusNoDocType DocName
CUS-022 1B1489-01 Rev 1
CUS-022 1B1489-01 Rev 1
CUS-022 1B149-02 Rev 2
CUS-022 1B149-02 Rev 2

View 2 Replies View Related

Basic Left Join

Feb 12, 2015

I want to join file a and file b where a.ent = b.ent and a.suf - b.suf i want one col from file b which is grc# even if blank.

Select * from mylib.filea a left join
hislib.fileb b
where a.ent = b.ent
and a.suf = b.suf

View 1 Replies View Related

Delete With Left Join

Feb 20, 2015

What is the logic of the below DELETE statement?It deletes all records where the file_modified column value is smaller then the maximum value in the same column, but not crystal clear how...

delete a
from Staging a
left join (select max(file_modifieddate) as file_modifieddate from Staging) b on
a.file_modifieddate = b.file_modifieddate
where b.file_modifieddate is null

View 2 Replies View Related

Left Outer Join

Dec 4, 2006

Good evening!

Well,in relational algebra we have left outer join.
In SQL is any command for that?
How can i use left outer join?

View 20 Replies View Related

Left Outer Join

Jan 15, 2007

I'm relatively new to SQL and I am wondering about when to use left outer joins. When should you use left outer joins?

View 2 Replies View Related

Left Join Error

Apr 27, 2007

I am trying to do a left join from one table a to table B on a column CEO_ID which is of datatype varchar(5) in table A and of datatye nvarchar(20) in table b.The resultset gives me all the rows that are in both the tables except a few..So when I looked at the records that are missing, it is stored as 00392 in table A and 392 in table b and that is the reason it is not showing that record in the result.How can I show these records?

Query
Select p.CEO_ID,p.Part_ID,ct.CEO_ID__c
from
Contact ct
left join
Main5.dbo.Part p
on
p.CEO_ID = Convert(varchar(5),ct.CEO_ID__c) collate database_default
where
ct.CEO_ID__c = 392

ResultSet

CEO_ID CEO_ID__c
------ ---------
NULL 392

Please help me !

View 2 Replies View Related







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