?
Solved

Join Sales and inventory tables

Posted on 2010-01-11
8
Medium Priority
?
407 Views
Last Modified: 2012-05-08
I am tryng to join a inventory table that has unique records based on a item number with a sales table that has multplie instances of that item 1 row per sale. How can I join the table togther while keeping the exact amount of rows in the inventory tables and summing the reords on sales side. I have done this wth sub selects before but I am looking for a beeter way.

Select  stock.number, items.item, items.unquants

from stock
join items on stock.number=items.item

where item like 'DV'
Select  stock.number, items.item, items.unquants

from stock
join items on stock.number=items.item 

where item like 'DV'

Open in new window

0
Comment
Question by:ubsmail
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 26287084
could you give the table definitiond of inventory and sales tables..
0
 
LVL 1

Expert Comment

by:CubicleGuy
ID: 26287088
You can
Select Distinct(stock.number), etc.,

You will still have to create the (Select sum(sales.dollars) as a column, but it should work.

Good luck.
0
 

Author Comment

by:ubsmail
ID: 26287250
Inventory table as mentioned above is stock
sales mentioned above is items
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 41

Expert Comment

by:ralmada
ID: 26287510
Do you mean like this?
If not please post some sample data and the expected result.

Select  stock.number, sum(items.item) as sum_item, sum(items.unquants) sum_unquants
from stock 
join items on stock.number=items.item  
where item like 'DV'
group by stock.number

Open in new window

0
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 26287533
Actually I don't know why you need the items.item column.
Select  stock.number, sum(items.unquants) sum_unquants 
from stock  
join items on stock.number=items.item   
where item like 'DV' 
group by stock.number

Open in new window

0
 

Author Comment

by:ubsmail
ID: 26287726
ralmada,

I like the simplicity of this solution however it is not producing the same number of rows as when I do a select, any suggestions?



Select  stock.number, sum(items.quants) sum_quants  
from stock  
join items on stock.number=items.item    
where item like '1SX%'  
group by stock.number
order by number


--returned 81 rows--


select number from stock where number like '1SX%' order by number


--returned 104 rows--
 
0
 

Author Comment

by:ubsmail
ID: 26287746
also tried this same result

Select  stock.number, sum(items.quants) sum_quants  
from stock  
join items on stock.number=items.item    
where stock.number like '1SX%'  
group by stock.number
order by number


--returned 81 rows--


select number from stock where number like '1SX%' group by stock.number order by number


--returned 104 rows--
 
0
 

Author Comment

by:ubsmail
ID: 26287753
this worked

Select  stock.number, sum(items.quants) sum_quants  
from stock  
left join items on stock.number=items.item    
where stock.number like '1SX%'  
group by stock.number
order by number


--returned 81 rows--


select number from stock where number like '1SX%' group by stock.number order by number


--returned 104 rows--
 
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question