sql - Displaying data from multiple tables in Oracle 11g -

-

question

i want display combination of data different tables. in case combinations of food , drink, have not been ordered far.

my database tables

customer_order

orderno(pk)  dateord    datereq    address o00001       03-apr-11  07-apr-11  union st o00002       05-apr-11  01-may-11  st. andrew st. o00003       12-apr-11  27-apr-11  garthdee o00004       12-apr-11  17-apr-11  union st.

dish

dishid(pk)  dishname         vegetarian   price d0001       pasta bake       yes           6.00 d0002       fish pie         no            9.00 d0003       steak , chips  no           14.00 d0004       stuffed peppers  yes          11.50 d0005       ham , rice     no            7.25 d0006       lamb curry       no            8.50

drink

drinkid(pk)  drinkname    drinktype  price dr0001       water        soft        1.00 dr0002       coffee       hot         1.70 dr0003       wine         alcoholic   3.00 dr0004       beer         alcoholic   2.30 dr0005       tea          hot         1.50

food_order

food_orderno(pk)  orderno(fk)  dishid(fk)  nportions fo00001           o00001       d0003         6 fo00002           o00001       d0001         4 fo00003           o00001       d0004         3 fo00004           o00002       d0001        10 fo00005           o00002       d0002        10 fo00006           o00003       d0002        35 fo00007           o00004       d0002        23

drink_order

drinkorderid(pk)  orderid(fk)  drinkid(fk)  n_units do00001           o00001       dr0001       13 do00002           o00001       dr0002       13 do00003           o00001       dr0004       13 do00004           o00002       dr0001       20 do00005           o00002       dr0003       20 do00006           o00002       dr0004       15 do00007           o00003       dr0002       35 do00008           o00004       dr0001       23 do00009           o00004       dr0003       15 do00010           o00004       dr0004       15

http://imgur.com/kzieolo

queries tried far

select distinct d.dish_name, dr.drink_name dish d, drink dr, food_order fo, drink_order dro, customer_order co co.orderno = fo.orderno   , co.orderno = dro.orderno order dish_name, drink_name;  select d.dish_name, dr.drink_name dish d, drink dr, food_order fo, drink_order dro, customer_order co co.orderno = fo.orderno   , co.orderno = dro.orderno group d.dish_name, dr.drink_name;  select distinct d.dish_name, dr.drink_name dish d, drink dr, food_order fo, drink_order dro, customer_order co d.dishid = fo.dishid   , dr.drinkid = dro.drinkid   , fo.orderno = dro.orderno group d.dish_name, dr.drink_name;  select distinct d.dish_name, dr.drink_name dish d, drink dr, food_order fo, drink_order fo.orderno = do.orderno group d.dish_name, dr.drink_name;

that seems return, asking for:

select   dish.dishname,   dish.dishid,   drink.drinkname,   drink.drinkid dish join   (select       dishid       , drinkid     dish     cross join drink     minus     (select       dishid       , drinkid     food_order     cross join drink_order     food_order.orderno = drink_order.orderid     )   ) notorderedtogether   on dish.dishid = notorderedtogether.dishid join drink   on notorderedtogether.drinkid = drink.drinkid order dishname, drinkname ;

see in action: sql fiddle.

as pointed out in comments, result set going explode, if add large(r) numbers of food , beverages. in case, make sense apply minus first dishes , drinks respectively, , apply cross join least possible number of records.

please comment, if , requires adjustment / further detail.


Comments

Post a Comment

Popular posts from this blog

python - No exponential form of the z-axis in matplotlib-3D-plots -

-
Image
i have similar problem described in how prevent numbers being changed exponential form in python matplotlib figure : i don't want (in special case) weird scientific formatting of axis. problem different have problem @ z -axis. 2-d plots can use ax.get_yaxis().get_major_formatter().set_useoffset(false) . , there no function ax.get_zaxis() what use format z -axis same way? edit: example: from mpl_toolkits.mplot3d import axes3d import numpy np import sys import matplotlib import matplotlib.pyplot pyplot def func(xi, ti): res = 10e3 + np.cos(ti) * np.sin(xi) return res if __name__ == '__main__': timespacing = 20 timestart = 0 timeend = 1 time = np.linspace(timestart, timeend, timespacing) widthspacing = 50 widthstart = 0 widthend = 3 width = np.linspace(widthstart, widthend, widthspacing) reslist = [] matplotlib.rcparams['legend.fontsize'] = 10 fig = pyplot.figure() ax = fig.gca(projection = '3d...
Read more

php - Best Light server (Linux + Web server + Database) for Raspberry Pi -

-
i install web server database on raspberry pi (little computer). computer has 1gb ram. i want know best combination: linux distribution , web server , dbms run local server multiple users minimal latency, use php on server. , best settings performance , not have bugs (memory usage, disable plugin, disable service, etc)? i thought light debian , lighttpd server , sqlite database. solution? i think lighttpd + sqlite great choice. linux distro, debian @ centos or tiny core linux , although i'm not sure of compatibility pi. obviously, can't go wrong raspbian if want use in production , more stable performance, few more pi's , set them in cluster .
Read more

c# - "Newtonsoft.Json.JsonSerializationException unable to find constructor to use for types" error when deserializing class -

-
i developing app in xamarin android. have splash screen serialize class , pass mainactivity using intent . when try deserialize in mainactivity error message : "serializationexception unable find constructor use types" serialization : void loaddata() { currency currency = new currency(this); intent = new intent(this, typeof(mainactivity)); intent.putextra("currency", newtonsoft.json.jsonconvert.serializeobject(currency)); startactivity(intent); } deserialization : cur = newtonsoft.json.jsonconvert.deserializeobject<currency> (intent.getstringextra("currency")); // error here class constructor : public currency(context context) { thiscontext = context; } i began experiencing problem after added parameter context context constructor. has caused this? how can solve it? possible serialize/deserialize class has parameters? you need have empty constructor in currency class in order deserialize it. ...
Read more