Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

Join Sales and inventory tables

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
ubsmail
Asked:
ubsmail
1 Solution
 
tigin44Commented:
could you give the table definitiond of inventory and sales tables..
0
 
CubicleGuyCommented:
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
 
ubsmailAuthor Commented:
Inventory table as mentioned above is stock
sales mentioned above is items
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ralmadaCommented:
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
 
ralmadaCommented:
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
 
ubsmailAuthor Commented:
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
 
ubsmailAuthor Commented:
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
 
ubsmailAuthor Commented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now