sql - Combining data from multiple tables issue Oracle 11g -

sql - Combining data from multiple tables issue Oracle 11g -

table // field customer // company stock // description manufact // manu_name items // quantity, total_price using oracle 11g application express. need display list of each stock ordered each customer. need display manufacturer, quantity ordered, , total cost paid.

when run query within sql*plus command prompt, endlessly displays results tables mentioned until force-quit (ctrl+c) application. incredibly frustrating - i've tried joining tables, using exists clause, don't know hell do. insight wonderful - not looking solve me, more-so guide me.

select c.company, s.description, m.manu_name, i.quantity, i.total_price db1.customer c bring together db1.orders o using (customer_num), db1.stock s, db1.manufact m, db1.items o.order_num = i.order_num;

this causes never-ending display of seems same results, over, , over, , over. essentially, need display required info each order of stock. however, don't need order_num in output display of columns, thought needed utilize order_num (in db1.orders o & db1.items i) tell oracle, "for each order_num (an order can't exist without order_num), display (results)...

i incredibly lost - i've tried outer joins, i've tried using exist operator, stumped , sense it's easy i'm overlooking.

thanks.

edit: so, seems found it, after enormous amount of pondering.

this how did it, in case else runs issue:

select c.company, s.description, m.manu_name, i.quantity, i.total_price db1.customer c bring together db1.orders o using (customer_num) bring together db1.items using (order_num) bring together db1.stock using (stock_num) bring together db1.manufact m on m.manu_code = s.manu_code order c.company, s.description; if join db1.manufact m using (manu_code), ambiguously defined column error oracle - because joined other tables , column in 1 of them (it db1.stock table). can still bring together them, have utilize bring together on instead.

this displayed results needed. anyways, , cheers if helped out!

you've provided 2 joins (one using , 1 in where) between 5 tables - in case, cartesian product of other rows in other tables, hence big number of rows.

(edit, implication need bring together tables together, whether using or join)

in order utilize using bring together sugar, same column must nowadays on immediate lhs , rhs tables. for multiple joins, hierarchy, may need nest usings so: select c.company, s.description, m.manu_name, i.quantity, i.total_price client c bring together orders o bring together stock s bring together items bring together manufact m using(manid) using(itemid) using (stockid) using (customer_num);

there where bring together isn't needed since have using join

i've assumed columns , relationships between table in this fiddle here:

you can drop using , utilize explicit join syntax, allow avoid nesting (this more portable across ansi world):

select c.company, s.description, m.manu_name, i.quantity, i.total_price client c inner bring together orders o on c.customer_num = o.customer_num inner bring together stock s on o.stockid = s.stockid inner bring together items on i.itemid = s.itemid inner bring together manufact m on m.manid = i.manid;

edit op has demonstrated, no requirement nest using joins, provided bring together ordering sensible, , provided fk bring together column isn't duplicated across multiple tables. http://sqlfiddle.com/#!4/91ef6/9

sql oracle table join oracle11g