I am retrieving inventory information from a Pervasive db using excel query. I need the inventory info from 2 different tables. I want the results on 1 worksheet. The product number is the common field in both tables so I use INNER JOIN to get the price for the product (Price table) and match it to the stock name and quantities (inventory table). However the results give me duplicates (sort of). I get results with each product listed on 2 rows. One row has all of the stock information from the Inventory table as well as the pricing from the Price Tables (just what I want) however there is also a second row with just the results from the inventory table and 0 for price. How do i fix this?
SELECT partnum, description, price1, wholesale, ave_cost, on_hand FROM inventory, pricing
WHERE partnum = pricepartnum