Left Join Returns Values Where I Was Was Expecting Nulls
Nov 16, 2006
I have a query which is returning a different result set when it is run against identical tables in 2 different environments.
The query is like:
Select
F.LicenseeID, IsSpecialLicensee
from FactTable F
left join View_SpecialLicensee SL on F.LicenseeID = SL.LicenseeID
The Create Statement for the view is like
Create View [dbo].[View_SpecialLicensee]
as
Select LicenseeID, LicenseeName, IsSpecialLicensee = 1
from DimensionLicensee
where LicenseeName like '%ibm%'
or LicenseeName like '%cisco%'
or LicenseeName like '%hp%'
In my test environment, I get the query result I expected:
LicenseeID, IsSpecialLicensee
1 , 1 - (where LicenseeName = 'IBM')
2, null - (where LicenseeName = 'Juniper')
3, 1 - (where LicenseeName = 'Cisco')
4, null - (where LicenseeName = 'Microsoft')
5, null - (where LicenseeName = 'Oracle')
6, null - (where LicenseeName = 'Apple')
In my production environment, I get the following query result:
1 , 1 - (where LicenseeName = 'IBM')
2, 1 - (where LicenseeName = 'Juniper')
3, 1 - (where LicenseeName = 'Cisco')
4, 1 - (where LicenseeName = 'Microsoft')
5, 1 - (where LicenseeName = 'Oracle')
6, 1 - (where LicenseeName = 'Apple')
Ideas as to what changed gratefully received.
FYI the production environment which returned the 2nd dataset is SQL2000, I have got the result I expected in both SQL2000 and SQL2005 development environments.
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]
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.
I have run into a problem that I am stuck on. I have 3 tables lets call them a,b, and c. What I want to do is left outer join a with b on a common value, then replace any null values in this result with a default value, and then left outer join table c on the previously joined table (on the columns that I just replaced certain values on).
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 have a SQL database where I am attempting to perform a complicated query that I cannot seem to figure out. I am using SQL Server.
I have 4 tables (TableA, TableB, TableC, and TableD). TableA and TableB are guaranteed to have a relationship.
TableC and TableD are guaranteed to have a relationship.
The trick is, I need to link between TableA and TableC essentially using a LEFT JOIN. I need to retrieve all of the values from TableA regardless and the information from TableC and TableD if there is a link, if there isn't a link, then the values from TableC and TableD need to be empty strings.
Does anyone know how I can do this? I've been trying for the last 5 hours without any luck. I feel I'm close, but there is something I feel I'm overlooking.
The Orders table contains orders placed on all the dates. I want to obtain a list of orders for a particular date, if there is no order for a product on the requested date, I want to return null values for the Quantity and Price fields.
I tried the following select statement:
select Products.ProductName, Orders.Quantity, Orders.Price from Products left join Orders on Products.ProductID = Orders.ProductID where Orders.OrderDate = '10/16/2004'
Where, there are a total of three products (A,B,C) in table Products. Product-C has no order on 10/16/2004, but I want it to return :
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.
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?
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?
I am getting VERY poor performance on a join even though I have indexes. Instead of using the indexes, SQL Server does a table scan on one of the tables in the join. After I force the index in the query, I can see why. The index doesn't give any better results than a table scan. I suspect it may be because the table has a huge number of rows with null values for one column of a two-column key used in the join. However, the same tables use the indexes and give good performance when queried independently. It seems the problem is specific to join processing. I notice that the index I created appears to have pages referencing the null rows. I know that Oracle disregards nulls when creating indexes, but does SQL Server not do so? Is there any way to create a better index? And if I can't solve the join problem, any suggestions about workarounds?
The details: -I have TABLE A with columns X, Y, and Z. In TABLE A there are 380,000 rows, and X and Y yield a unique key. The index is on X and Y. This table has 240,000 rows where X is null but there is a non-null value for Y. -I have TABLE B (60,000 rows) with columns X,Y, and Q, where X,Y, and Q yield the unique key and have an index. -Here is my query: SELECT A.Z FROM A, B WHERE B.Q=1 AND B.X = A.X AND B.Y = A.Y
I want to join 2 tables by a unique ID field, but the ID field also hasmultiple NULLS which I do not want to ignore and I fear they will causeduplication.Using TableA and TableB below i will demonstrate the problem.TableATableA.ID Field1 Field21 Paul 1Null John 12 John 1TableBTableB.ID Field3 Field41 25 1Null 32 1Null 23 12 26 1The Table I want isTableA.ID TableB.ID Field1 Field2 Field3 Field41 1 Paul 1 25 12 2 John 1 26 1Null Null John 1 Null NullNull Null Null Null 32 1Null Null Null Null 26 1I think a select distcinct statement with a full outer join may do whatI want, but I'm not certain so want to check.Regards,Ciarán
I need to pull all records from the Item table and then I need to populate the most recent OrderNo and O.DateCreated. I got this far but if there is a part in the item table that does not have an order against it, I do not get a value and my goal is to see any parts that have not been ordered in the last year. Something like this:
SELECT I.PartNumber, I.Description, I.DateCreated FROM item I CROSS APPLY (SELECT TOP 1 O.OrderNo, O.DateCreated FROM Orders O WHERE O.PartNumber = I.PartNumber ORDER BY O.DateCreated DESC) O PartNumberOrderNoO.DateCreated 1A1XXX 1CHXX1 1/8/2014 2A2XXX 1CHXX3 1/20/2014 3A3XXX NULL NULL 1B1XXX 2CHXX1 2/10/2014 2B2XXX 2CHXX3 2/22/2014 3B3XXX NULL NULL
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.
3) select p.komorka,isnull(sum(ustalenia),0) from #plantemp p left join analiza_1 a on p.komorka=a.komorka where a.koniec between '20040701'and '20040731' group by p.komorka
komorka ustalenia (sum)
08.0000 SI/1788138.9300 SI/262856.8900
I need all rows from table 1 bat right and left join gives me the same results, WHY
As you can see the ParentIndexID and ChildIndexID fields refer to tblIndices.IndexID I would like a stored procedure as follows: show all index names and show the wights for the indexID you passed. This is what I have so far and it is not correct yet. Not sure what the syntax should be.
alter PROCEDURE [dbo].[uspBasketIndices_Get]
@IndexIDint
AS
select i.IndexID, i.[Name], bc.Weight from tblIndices as i left join tblBasketConstituents as bc on i.IndexID = bc.ParentIndexID and i.IndexID = @IndexID order by i.[Name]
Any one know any facts and figures about maximum Left Joins allowed (or recommended) in one query?
I am running a MS SQL 2000 my database is full of relational data and most of my foreign keys (INT data type) are a Clustered Indexed, Usually I will only be pulling one record from collection of about a dozen tables, but the Database is expected to grow fast and become big.
Right now I have a Stored Proc that has eight(8) LEFT JOINs in it. My worry is that this query will kill me as the database approaches 50,000 records.
Hi all. My query works fine, it generates reports but not my expected result.
select d.fullname, p.nickname, p.birthdate, p.birthplace, p.gender, p.civilstatus, p.religion, p.nationality, p. weight, p.height, p.haircolor, p.eyecolor, p.complexion, p.bodybuilt, p.picture, p.dialectspoken, d.mobilephone, d.prprovince,[Age] = dbo.F_AGE_IN_YEARS( birthdate, getdate() ), c.name, c.address, c.telno, c.email, c.occupation, ed.year1, ed.year2, ed.degree, sch.schname from hremployees as e inner join psdatacenter as d on e.empdcno = d.dcno inner join pspersonaldata as p on e.empdcno = p.dcno left join hrappempcharrefs as c on e.empdcno = c.empdcno left join hrappempeducs as ed on e.empdcno = ed.empdcno left join hrsetschools as sch on ed.schoolcode = sch.schcode
the above query gives a 77 records
if i ran "select * from hremployees" generates 60 records
i think the error is in the left joining. hrappempcharrefs, hrappempeducs and hrsetschools must be left joined to hremployees.
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
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.
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
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.
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.
