May 12, 2014 at 12:23 pm
Hi
I have 3 tables , Customer , Sales Cost Charge and Sales Price , i have join the customer table to the sales price table with a left outer join into a new table.
i now need to join the data in the new table to sales cost charge. However please note that there is data that is in the sales price table that is not in the sales cost charge table and there is data in the sales cost charge table that is not in the sales price table ,but i need to get all the data. e.g. if on our application it shows 15 records , the sales price table will maybe have 7 records and the sales cost charge table will have 8 which makes it 15 records
I am struggling to match the records , i have also tried a left outer join to the sales cost charge table however i only get the 7 records which is in the sales price table. see code below
SELECT
a.[No_],
a.[Name],
a.[Currency Code],
a.[Salesperson Code],
b.[Sales Code],
b.[Item No_],
b.[Item Description],
b.[Unit Price] AS SalesPriceUnitPrice,
b.[Starting Date] AS SalesPriceStartDate,
b.[Ending Date] AS SalesPriceEndDate
INTO [dbo].[Sales_Price]
FROM [dbo].[Spier Live$Customer]a
Left outer join [dbo].[Spier Live$Sales Price] b
ON b.[Sales Code] = a.[No_]
SELECT
a.[No_],
a.[Name],
a.[Currency Code],
a.[Salesperson Code],
a.[Item No_],
a.[Item Description],
a.[SalesPriceUnitPrice],
a.[SalesPriceStartDate],
a.[SalesPriceEndDate],
c.[Item Charge Code],
c.[Unit Price] AS CostPriceUnitPrice,
c.[Starting Date] AS [CostPriceStartDate],
c.[Ending Date] AS [CostChargeEndDate]
INTO Sales_Cost_Charge
FROM Sales_Price a
LEFT OUTER JOIN [dbo].[Spier Live$Sales Cost Charge] c
ON c.[Sales Code] = a.[No_]
Thanks in advance for your help
May 12, 2014 at 12:32 pm
Hi and welcome to the forums. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
Also, you might consider using aliases that are relevant to the objects. If you just always use a,b,c it is difficult to know which table you are referring to because the alias changes in each and every query. In your first query for example it would be easier to work with if you used something like this:
FROM [dbo].[Spier Live$Customer] slc
Left outer join [dbo].[Spier Live$Sales Price] slsp
ON slsp.[Sales Code] = slc.[No_]
It isn't a huge deal but that kind of thing will help you and other be more efficient because you don't have to decipher the alias all the time. 🙂
_______________________________________________________________
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/
May 12, 2014 at 4:08 pm
So, I'm unclear how your tables map together. What are the primary and foreign keys that define the relationships. That ought to drive us towards a meaningful query. As it stands now, it almost sounds like things are not related, in which case, if you're trying to bring it all back as a single result set, maybe you want to look at UNION or UNION ALL.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 12, 2014 at 6:43 pm
You cannot use union as all the tables does not contain the same columns. the primary key in the customer table is NO_ the key in the Sales Price table is Sales Code. Now Sales Code and NO contains the same information e.g. F000214 , we join customer on No_ = Sales code in the Sales Price table into a new table
once i have that information i need to join Sales Cost Charge table to the new table on No_ = Sales Code
i get duplicates when i do that and if i use full outer joins i get null values which is not suppose to be null
it is difficult for me to send the infor you requested as the table data are huge , the tables were pre generated when we installed the application , the data were entered in the GUI front end as users were entering data
May 13, 2014 at 4:35 am
OK. I think I somewhat understand now. The thing is, you're going to NULL values, you have to. You have a mismatched set of rows across the tables, so some are going to have values where others don't. That's how querying SQL Server is going to work. It sounds like you just need a left join. I'd use the principal table as the basis and join to the other two tables that way.
And, while it's not what you need in this case, you can make UNION work. You just dummy out all the columns across all the queries using aliases.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply