SQL; Full left, right, upper, inner, backwards joins -

can't seem head around this. have 3 tables (stock,sales,pos) relating products 1 common field can join them toghether. have fourth table has products in it. product may or may not have information in these tables (stock,sales,pos) have record in product table.

i need end 1 row per product has information in 1 of 3 tables (stock, sales , pos).

a simplified view of tables below:

product table, ref, description

stock table, ref, current_stock

sales table,ref, qty_sold

pos table, ref, qty_outstanding

i need end following:

ref, description, current_stock, qty_sold, qty_outstanding

thanks

nig

perhaps, easiest , straightforward way left-join detail tables product table, filter out products have no matches @ all.

so, @santhosh's solution small tweak (emphasized):

select   pr.ref,   pr.description,   st.current_stock,   sa.qty_sold,   po.qty_outstanding product pr left join stock st on st.ref = pr.ref left join sales sa on sa.ref = pr.ref left join pos   po on po.ref = pr.ref where st.ref not null    or sa.ref not null    or so.ref not null ; 

there's less obvious alternative: union detail tables, pivot detail data. resulting set contain references products have @ least detail information. so, inner join resulting set product access product descriptions output.

if sql product supports pivot clause, this:

select   p.ref,   p.description,   s.current_stock,   s.qty_sold,   s.qty_outstanding (   select     ref,     current_stock,     qty_sold,     qty_outstanding   (     select       ref,       'current_stock' attribute,       current_stock   value     stock     union     select       ref,       'qty_sold',       qty_sold     sales     union     select       ref,       'qty_outstanding',       qty_outstanding     pos   ) d   pivot (     sum(value) attribute in (       current_stock,       qty_sold,       qty_outstanding     )   ) p ) s inner join product p on p.ref = s.ref ;

there's older , more universal method of pivoting, need employ grouping , conditional aggregation:

select   p.ref,   p.description,   sum(case attribute when 'current_stock'   d.value end) current_stock,   sum(case attribute when 'qty_sold'        d.value end) qty_sold,   sum(case attribute when 'qty_outstanding' d.value end) qty_outstanding (   select     ref,     'current_stock' attribute,     current_stock   value   stock   union   select     ref,     'qty_sold',     qty_sold   sales   union   select     ref,     'qty_outstanding',     qty_outstanding   pos ) d inner join product p on p.ref = d.ref group   p.ref,   p.description ;