Problem with left join.

Question

Problem with left join.

igngua

SSCrazy

Points: 2510
More actions
December 19, 2011 at 2:25 pm

#253677

The following sentence works perfectly;
select distinct a.Sample_Number,a.Test_Group,a.ANACOD,b.codprod
from SAMPLE_ANALYSIS a
left outer join nwdetcot b
on a.Test_Group COLLATE Modern_Spanish_CI_AS = b.CodProd
where Sample_Number like '106587%'
order by Sample_Number
when i add 'b.ctprecio' column i get 5000 results and i should be getting only 50
select distinct a.Sample_Number,a.Test_Group,a.ANACOD,b.codprod,b.ctprecio
from SAMPLE_ANALYSIS a
left outer join nwdetcot b
on a.Test_Group COLLATE Modern_Spanish_CI_AS = b.CodProd
where Sample_Number like '106587%'
order by Sample_Number
'b.ctprecio' is the price of one product. Any product may or may not have a price.
so what i´m doing wrong??
thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i'm guessing nwdetcot is a list of all possible prices, possibly for all products thru the begfinning of time..i'm betting you need to limit it to just the CURRENT price.

what limits nwdetcot to the current price, so you don't get the last 50 time it went on sale and toggled back to regular price?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

igngua SSCrazy Points: 2510 More actions · Answer 2

Lowell (12/19/2011)
i'm guessing nwdetcot is a list of all possible prices, possibly for all products thru the begfinning of time..i'm betting you need to limit it to just the CURRENT price.
what limits nwdetcot to the current price, so you don't get the last 50 time it went on sale and toggled back to regular price?

I´m so f%&?ing noob. I forgot that completely.

So another question. The code now looks as follows;

select distinct a.Sample_Number,a.Test_Group,a.ANACOD,b.codprod,b.CtPrecio

from SAMPLE_ANALYSIS a

left join LAB0708r.softland.nwdetcot b

on a.Test_Group COLLATE Modern_Spanish_CI_AS = b.CodProd

where Sample_Number like '106587%' and b.CotNum = '12712'

order by Test_Group

But im getting only the results with a match for cotnum = '12712'. That makes up 20 records.

IF im doing a left join shouldnt i be getting the 50 results with the null values where apporpiate???

Thanks loweell

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

igngua (12/19/2011)
Lowell (12/19/2011)
i'm guessing nwdetcot is a list of all possible prices, possibly for all products thru the begfinning of time..i'm betting you need to limit it to just the CURRENT price.
what limits nwdetcot to the current price, so you don't get the last 50 time it went on sale and toggled back to regular price?
I´m so f%&?ing noob. I forgot that completely.
So another question. The code now looks as follows;
select distinct a.Sample_Number,a.Test_Group,a.ANACOD,b.codprod,b.CtPrecio
from SAMPLE_ANALYSIS a
left join LAB0708r.softland.nwdetcot b
on a.Test_Group COLLATE Modern_Spanish_CI_AS = b.CodProd
where Sample_Number like '106587%' and b.CotNum = '12712'
order by Test_Group
But im getting only the results with a match for cotnum = '12712'. That makes up 20 records.
IF im doing a left join shouldnt i be getting the 50 results with the null values where apporpiate???
Thanks loweell

Well you also said to only return those records where b.CotNum = '12712'. If you still want those records with a null you will either need to remove that in the where clause (or you might add it to your join condition). the second option would make it part of the join condition which would indicate you only want to find matches when the cotNum = '12712'. Hope that helps.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

As Sean pointed out, your query is only limited to the records that match the conditions of your WHERE clause. If you need more records than that, then you should change that clause (filter) to allow a different recordset to be presented to you.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

igngua SSCrazy Points: 2510 More actions · Answer 5

Thanks to everyone. Your input really help me into find the solution.