Sorry if this isn’t the place for this question but i thought it’s the best place to start.
We have some workbooks setup to extract data from our stock and sales system. I’m trying to add one piece of data from a table not mentioned in the SQL query. Below SQL set of code works perfectly but i also want to add data from a table called sopdet and the location from sopdet is called std_price. Does anyone know what i need to write in SELECT, FROM and WHERE to make this work. I guess in select it will be sopdet.std_price.
SELECT stock.depot, stock.part, stock.desc1, stock.desc2, stock.product_group, grpdesc.description, qty_hand-qty_aloc, stock.cost_fixed, stock.qty_order, stock.main_supplier, supplier.name, stock.purch_mtd, stock.purch_ytd, stock.units_mtd, stock.units_ytd, stock.sale_mtd, stock.sale_ytd
FROM public.grpdesc grpdesc, public.stock stock, public.supplier supplier
WHERE stock.company = grpdesc.company AND stock.product_group = grpdesc.id AND stock.main_supplier = supplier.account AND stock.company = supplier.company AND ((stock.company=1) AND (grpdesc.record_type=‘G’) AND (stock.depot=‘01’) AND (stock.product_group<>‘22’))
I was told to try adding the follwoing. When i do i get no error but it freezes and i have to exit every time so don’t get any result.
Select sopdet.std_price
and
From public.sopdet sopdet
4 Spice ups
craigrrr
(CraiGrrr)
2
You can add the table.column to the select list, but then you need to JOIN the table into the query on some common column value in the JOIN statements, which should fall within the FROM statement.
Lets say stock.part has the matching value to a hypothetical (or maybe not..) column in sopdet, sopdet.part.
So if I wanted to select the one column from one and the other column from the other table where it matches the common column of the first table:
SELECT stock.part, sopdet.std_price
FROM stock LEFT JOIN sopdet ON stock.part = sopdet.part
WHERE …etc…
(Note that the LEFT JOIN returns all values from the left-side table and if there is no matching from the right-side table it will return a NULL for that column. If you just use a JOIN it will only return both columns where they have a match in the common column.)
1 Spice up
