Left outer Join

Question

Left outer Join

jason.kleinhans

SSC Journeyman

Points: 88
More actions
May 2, 2014 at 5:55 am

#288378

Good day
I have 3 tables , Customer , Sales Cost Charge and Sales Price
the Customer table will be joined to Sales Cost Charge
select b.[Name], b.[No_], c.[Item No_], c.[Sales Code], b.[Currency Code], c.[Item Charge Code], b.[Customer Posting Group],
b.[Salesperson Code], c.[Unit Price], c.[_ of Item Price],
c.[Starting Date], c.[Ending Date]
into Cost_Price
from [dbo].[Spier Live$Customer] b,
[dbo].[Spier Live$Sales Cost Charge] c
where b.[No_] = c.[Sales Code]
Then i select all the columns into a new table again from the new table created in my first select statement above
then i left outer join Sales price and join Sales price to the new table created on Item No
However i am not retrieving the sales cost unit price for my customers i am only getting Sales price for the customer , how can i can both sales price and sales cost for all customers.
SELECT DISTINCT [No_], [Name], d.[Currency Code], d.[Salesperson Code], d.[Item No_], a.[Item Description],
a.[Unit Price] as SalesPriceUnitPrice, a.[Starting Date] as SalesPriceStartDate, a.[Ending Date] as SalesPriceEndDate,
d.[Item Charge Code], d.[Unit Price] as CostChargeUnitPrice, d.[_ of Item Price],
d.[Starting Date] as CostChargeStartDate, d.[Ending Date] as CostChargeEndDate
INTO SalesCost_SalesPrice
FROM Cost_Price d
LEFT OUTER JOIN [dbo].[Spier Live$Sales Price] a
ON d.[Item No_] = a.[Item No_]
AND d.[Sales Code] = a.[Sales Code]

Viewing 3 posts - 1 through 2 (of 2 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Which table contains the column which holds 'sales cost unit price'?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jason.kleinhans SSC Journeyman Points: 88 More actions · Answer 2

The Sales Cost Charge Table it the table that holds the Sales Cost Unit Price . the original column name is Unit Price , due to there being 2 unit price columns i gave it an alias . the one unit price column which comes from the sales price table is called SalesPriceUnitPrice and the unit price in the sales cost charge table is called CostChargeUnitPrice