sql - Query on table joined with itself -


today have final exam. approved, happily :d 1 of problems blowing mind.

i need help, can rest in peace.

the problem

we have table "people"

(pk)id | name | fatherid     ---------------------     1  | gon  |   2     2  | cesar|   6     3  | luz  |   2     4  | maria|   5     5  | diego|   6     6  | john |  -

this example of data. table has relation itself, on table fatherid(fk) table id(pk) need query show me 2 columns, in 1 name of person, , in one, his/her cousin.

pretty simple until here, right? problem have restrictions

  • only ansi allowed. no t-sql, or one. also, ansi 99 standard, not 2003 or higher
  • subquerys not allowed. , worst:
  • no relations repeated.

for example, considering in example, gon , maria cousins. if show, gon | maria in results, can't show maria | gon.

so, how can this? burning head.

what tried?

well, big problem in last requisite, repetition of data. ignoring that, put on exam (knowing wrong..)

select p3.name oneperson, p4.name cousin people p1 inner join people p2 on p1.fatherid = p2.fatherid , p1.id != p2.id inner join people p3 on p1.id = p3.fatherid inner join people p4 on p1.id = p4.fatherid

of course, not solving last requeriment, , have 4 in test(we pass 4) anyway, head burning. please, me!

another options explored

one of friends, had same exam said me

"well, considering every relation duplicated, can use top count(*) , order , half correct"

but.. top not ansi!

you can add query where p3.id < p4.id. eliminate duplicate results gon | maria , maria | gon.


Comments

Post a Comment

Popular posts from this blog

python - How to create a legend for 3D bar in matplotlib? -

Image
given ax = plt.subplot() : ax.bar()[0] can passed plt.legend() . however, ax.bar3d() returns none . how create legend displayed bars? update: passing legend="stuff" ax.bar3d() , calling ax.legend() raises /usr/lib/python2.6/site-packages/matplotlib/axes.py:4368: userwarning: no labeled objects found. use label='...' kwarg on individual plots. warnings.warn("no labeled objects found. " you need use proxy artist legends not supported. this code: from mpl_toolkits.mplot3d import axes3d import matplotlib.pyplot plt import numpy np fig = plt.figure() ax = fig.add_subplot(111, projection='3d') x, y = np.random.rand(2, 100) * 4 hist, xedges, yedges = np.histogram2d(x, y, bins=4) elements = (len(xedges) - 1) * (len(yedges) - 1) xpos, ypos = np.meshgrid(xedges[:-1]+0.25, yedges[:-1]+0.25) xpos = xpos.flatten() ypos = ypos.flatten() zpos = np.zeros(elements) dx = 0.5 * np.ones_like(zpos) dy = dx.copy() dz = hist.flatten() ax...
Read more

java - Multi-Label Document Classification -

i have database in store data based upon following 3 fields: id, text, {labels}. note each text has been assigned more 1 label \ tag \ class. want build model (weka \ rapidminer \ mahout) able recommend \ classify bunch of labels \ tags \ classes given text. i have heard svm , naive bayes classifier, not sure whether support multi-label classification or not. guides me right direction more welcome! the basic multilabel classification method one-vs.-the-rest (ovr), called binary relevance (br). basic idea take off-the-shelf binary classifier, such naive bayes or svm, create k instances of solve k independent classification problems. in python-like pseudocode: for each class k: learner = svm(settings) # example labels = [class_of(x) == k x in samples] learner.learn(samples, labels) then @ prediction time, run each of binary classifiers on sample , collect labels predict positive. (both training , prediction can done in parallel, since problems assumed ...
Read more

php - Dynamic url re-writing using htaccess -

how can rewrite http://mydomain.com/test/master.php?catid=2&subcatid=8 http://mydomain.com/test/master.php/catname/subcat using .htaccess file. once rewritten, how can access these parameters in master.php? note: untested, let me know if doesn't work. rewriterule ^master.php/([a-za-z0-9_-]+)/([a-za-z0-9_-]+)$ /master.php?catid$1&subcatid=$2 [l]
Read more