mysql - Eliminate tuples with reverse relation and no primary Key -

-

i trying insert relations based on question below. have got far, relations c (as per question below). get, getting records getting "c friends a". far understand question statement "no duplicate friendships", have insert friendship c , not vice-versa. either understand problem wrong, or can't desired result.
so, when try insert table values i've got, result wrong. persons have friends more desired, value of 2.

the structure of tables follows:

friend ( id1, id2 )

the student id1 friends student id2. friendship mutual, if (123, 456) in friend table, (456, 123).

(no primary key) situation trying solve is:

"for cases friends b, , b friends c, add new friendship pair , c. not add duplicate friendships, friendships exist, or friendships oneself."

i have been trying solve problem 2 days now. please help.

thanks in advance.

----my sql query-----

select b.id1 id1,b.id3 id2 (select a.id1 id1,a.id2 id2,a.id3 id3,f3.id2 id4 (select f1.id1 id1,f1.id2 id2,f2.id2 id3 friend f1 join friend f2 on f1.id2=f2.id1 f1.id1<>f2.id2) join friend f3 on a.id3=f3.id1) b b.id1<>b.id4 group  b.id1,b.id3

let's assume (for examples) friend table contains these rows.

id1 id2 --- ---    b    c  b    b   c  b   d  c   b

first, start identifying 'full friend' tuples friend table, query this:

select fa.id1      , fa.id2   friend fa   join friend fb     on fb.id1 = fa.id2    , fb.id2 = fa.id1  fa.id1 fa.id2 ------ ------        b   b        b      c   c      b

this result shows friends b, , b friends c. (a,c) , (b,d) rows omitted because there no inverse, (c,a) or (d,b).

for time being, we'll refer set "ft" (friend tuples). can write query against set (ft), "a->b->c" , "c->b->a" friend pairs.

select fx.id1      , fy.id2   ft fx   join ft fy     on fy.id1 = fx.id2     , fy.id2 <> fx.id1  fx.id1 fy.id2 ------ ------        c   c

but, need sure don't duplicate rows in friend table, use not in or not exists predicate, or can use anti-join pattern, eliminate rows match row in friend table.

select fx.id1      , fy.id2   ft fx   join ft fy     on fy.id1 = fx.id2     , fy.id2 <> fx.id1   -- eliminate rows match   left   join friend fe     on fe.id1 = fx.id1    , fe.id2 = fy.id2  fe.id1 null  fx.id1 fy.id2 ------ ------   c

now, can replace references ft query (as inline view) produces set:

select fx.id1      , fy.id2   ( select fa.id1               , fa.id2            friend fa            join friend fb              on fb.id1 = fa.id2             , fb.id2 = fa.id1        ) fx   join ( select fc.id1               , fc.id2            friend fc            join friend fd              on fd.id1 = fc.id2             , fd.id2 = fc.id1        ) fy     on fy.id1 = fx.id2     , fy.id2 <> fx.id1   -- eliminate rows match   left   join friend fe     on fe.id1 = fx.id1    , fe.id2 = fy.id2  fe.id1 null  group      fx.id1      , fy.id2

(i'm thinking long guaranteed (id1,id2) unique, query won't generate duplicates. , i'm thinking query generate matches specified, , not matches. additional test cases in order confirm. if query produce duplicates, adding group fx.id1, fy.id2 query eliminate them.)

finally, put rows friend table, precede query with:

insert friend (id1,id2)

update

the result want returned depends on how "friendship" represented.

i assuming "friend" pair represented in friend table existence of 2 tuples: both (a,b) , (b,a) have exist. (a friendship formed when "a friends b", , "b friends a).

if 1 of rows exists, it's not real friendship, halfway friendship.

i ran several test cases. it's kind of tedious working through them. expanded query adding order rows in deterministic order, , adding additional columns in select list, verify "path" (shared friend). commented out clause, see potential friends.

i did find needed add group by eliminate duplicates. can derive a-c friendship 2 or more shared friends e.g. b , r. both a-b + b-c , a-r + r-c result in a-c.

this final query tested. it's equivalent previous, except addition of group by.

select fx.id1      , fy.id2  --  , fx.id1>fy.id2 d  --  , fx.id1 x1  --  , fx.id2 x2  --  , fy.id1 y1  --  , fy.id2 y2  --  , fe.id1 e1  --  , fe.id2 e2   ( select fa.id1               , fa.id2               , fa.id1>fa.id2 d            friend fa            join friend fb              on fb.id1 = fa.id2             , fb.id2 = fa.id1        -- order        --    least(fa.id1,fa.id2)        --     , greatest(fa.id1,fa.id2)        --     , fa.id1>fa.id2        ) fx   join ( select fc.id1               , fc.id2            friend fc            join friend fd              on fd.id1 = fc.id2             , fd.id2 = fc.id1        -- order        --    least(fc.id1,fc.id2)        --     , greatest(fc.id1,fc.id2)        --     , fc.id1>fc.id2        ) fy     on fy.id1 = fx.id2     , fy.id2 <> fx.id1   -- eliminate rows match existing row   left   join friend fe     on fe.id1 = fx.id1    , fe.id2 = fy.id2  fe.id1 null  group     fx.id1      , fy.id2  order     least(fx.id1,fy.id2)      , greatest(fx.id1,fy.id2)      , fx.id1>fy.id2

if full friendship represented existence of 1 tuple "(a,b)" implies "(b,a)", query need changed.

the inline view query fx , fy need expanded return "missing" inverse tuples... if (a,b) in friend table, our query needs return both (a,b) , (b,a). we'd accomplish doing union operation between 2 identical queries, order of columns in select list reversed. (here, make use of union instead of union eliminate duplicates.) inline view query fx , fy like:

select fa.id1, fa.id2 ...  union select fa.id2, fa.id1 ...

the check eliminate matching rows in friend table need changed (we'd want eliminate both (a,b) , (b,a) resultset if found existing (a,b) or (b,a) row)

on ( fe.id1 = fx.id1 , fe.id2 = fy.id2 ) or ( fe.id1 = fy.id2 , fe.id2 = fx.id1 )

and select list , group need changed eliminate "extra" inverse tuple. use expression in order 

select least(fx.id1,fy.id2) id1      , greatest(fx.id1,fy.id2) id2        ...  group     least(fx.id1,fy.id2)      , greatest(fx.id1,fy.id2)

Comments

Post a Comment

Popular posts from this blog

java - Run a .jar on Heroku -

-
i trying run .jar file on heroku, , can't find instructions anywhere on how this. have read wants me compile uploading heroku. possible, , if is, how this? if helps, trying run minecraft craftbukkit server, , don't have pom.xml file yet. you didn't provide details i'll try give , people reaches question way it. first of all, create heroku account , create heroku application (assumed have done that). second, need generate application jar , upload heroku. so, open terminal , install heroku cli (assuming unix terminal): $ sudo npm install -g heroku-cli you need have npm , nodejs installed, can check link: https://www.digitalocean.com/community/tutorials/how-to-install-node-js-on-ubuntu-16-04 once done that, can use npm update node version acceptable heroku-cli: $ sudo npm install -g n $ sudo n 7.10.0 and run: $ sudo npm install -g heroku-cli in order deploy jars, need install deploy plugin: $ sudo heroku plugins:install heroku-cli-deploy ...
Read more

java - Jtable duplicate Rows -

-
Image
when add new rows in jtable duplicate rows many times this method update jtable public final void tableaucomande(){ ((defaulttablemodel)tabcommande.getmodel()).getdatavector().clear(); session s=hibernateutil.getsessionfactory().opensession(); criteria cr1= s.createcriteria(commande.class); list <commande> lcs=cr1.list() ; criteria cr2= s.createcriteria(lignecommande.class); list <lignecommande> lck=cr2.list() ; if(lcs.size()!=0 && lck.size()!=0) { (commande lc:lcs) { for(lignecommande lig:lck){ ((defaulttablemodel)tabcommande.getmodel()).addrow(new object[]{ lc.getid_cmd(),lc.getnumcmd(),lc.getdate_cmd(),lc.getclient().getnomprenom(), lig.getproduit().getlib_prd(),lig.getquantite(),lig.getproduit().getid_prd() }); } } // tabcommande.setrowselectioninterval(tabcommande.getrowcount()-1,tabcommande.getrowcount()-1); } } this code add add data rows displaye...
Read more

validation - How to pass paramaters like unix into windows batch file -

-
i need pass various parameters .cmd file unix format file.cmd -configuration=value -source=value -flag but, try this: startlocal @echo off cls setlocal set cmdline=%* set configuration= set source= set badargs= set validation= goto main :splitargs echo splitargs(%*) if "%*" neq "" ( /f "tokens=1,2,* delims== " %%i in ("%*") call :assignkeyvalue %%i %%j & call :splitargs %%k ) goto :eof :assignkeyvalue echo assignkeyvalue(%1, %2) if /i %1==-configuration ( set configuration=%2 ) else if /i %1==-source ( set source=%2 ) else ( rem append unrecognised [key,value] badargs echo unknown key %1 set badargs=%badargs%[%1, %2] ) goto :eof :validate echo validating set validation=fail if defined configuration ( echo -configuration ok if defined source ( echo -source ok if not defined badargs ( set validation=success ) ) ) goto :eof :main cl...
Read more