MegaPowerRockstar Posted May 23, 2014 Author Report Posted May 23, 2014 ekada matching records kadhu concept..... I need to apply a formula for each record in table 2 with all rows in table 1,... And need to find out the minimum difference...
MegaPowerRockstar Posted May 23, 2014 Author Report Posted May 23, 2014 SELECT A.ID as A_ID, B.ID as B_ID, A.tb1_col1 as ACol1, B.tb1_col1 as BCol1 A.tb2_col2 as ACol2, B.tb2_col2 as BCol2 from TABLE1 A, TABLE2 B where A.ID = B.ID For this query you will get A_ID, B_ID, Acol1,Bcol1, Acol2,Bcol2 then you can import these into excel to compare them.. or you can compare them in the query as well. here my situation is not make a join.... but need to apply a formula by taking each row in tablee 2 with all rows in table 1 and then find the minimum difference...
150bryant Posted May 23, 2014 Report Posted May 23, 2014 select A.ID as A_ID, B.ID as B_ID, A.tb1_col1 as ACol1, B.tb1_col1 as BCol1 A.tb2_col2 as ACol2, B.tb2_col2 as BCol2 A.tb1_col1 - B.Tb1_col1 as Col1Diff, A.tb2_col2- B.tab2_col2 as Col2Diff from TABLE1 A, TABLE2 B where A.ID = B.ID order by A.tb1_col1 - B.Tb1_col1, DESC this will give Max diff...for TB1 Col1 for min use ASC
MegaPowerRockstar Posted May 23, 2014 Author Report Posted May 23, 2014 select A.ID as A_ID, B.ID as B_ID, A.tb1_col1 as ACol1, B.tb1_col1 as BCol1 A.tb2_col2 as ACol2, B.tb2_col2 as BCol2 A.tb1_col1 - B.Tb1_col1 as Col1Diff, A.tb2_col2- B.tab2_col2 as Col2Diff from TABLE1 A, TABLE2 B where A.ID = B.ID order by A.tb1_col1 - B.Tb1_col1, DESC this will give Max diff...for TB1 Col1 for min use ASC dude....firstly Id is a common key of both tables... and your query it takes the difference of one-one.... but my situation is one row of table 2 to all rows of table 1 and then of all the difference i need to find minimum....
150bryant Posted May 23, 2014 Report Posted May 23, 2014 dude....firstly Id is a common key of both tables... and your query it takes the difference of one-one.... but my situation is one row of table 2 to all rows of table 1 and then of all the difference i need to find minimum.... do a cross join..it will connect all ids to all ids.. then retrieve the col1 col2 values from both tables.. from there u can derive the minimum value of A.Col1-B.col1 for all ids..
MegaPowerRockstar Posted May 23, 2014 Author Report Posted May 23, 2014 do a cross join..it will connect all ids to all ids.. then retrieve the col1 col2 values from both tables.. from there u can derive the minimum value of A.Col1-B.col1 for all ids.. sample query .......... pls
150bryant Posted May 23, 2014 Report Posted May 23, 2014 sample query .......... pls http://www.sqlguides.com/sql_cross_join.php baaga illustrate chesadu ikkada. simple way to write is select * from table1, table2 anthey no filters no joins..u get everythin
MegaPowerRockstar Posted May 23, 2014 Author Report Posted May 23, 2014 http://www.sqlguides.com/sql_cross_join.php baaga illustrate chesadu ikkada. simple way to write is select * from table1, table2 anthey no filters no joins..u get everythin thanks dude....may be this might work..but still there is a complexity to my situation...may be i can figure it outt...
budhimantudu Posted May 23, 2014 Report Posted May 23, 2014 thanks dude....may be this might work..but still there is a complexity to my situation...may be i can figure it outt... bhayya naku question clear ga rdam kaledu... neeku result set em kavali post chei .... dont explain post the result set..
MegaPowerRockstar Posted May 23, 2014 Author Report Posted May 23, 2014 bhayya naku question clear ga rdam kaledu... neeku result set em kavali post chei .... dont explain post the result set.. here my requirement is not a result set...... i need to calculate based on those table data... anyways, i have figured out using cross join
150bryant Posted May 23, 2014 Report Posted May 23, 2014 please post your answer...after you solved it. might be useful for others
MegaPowerRockstar Posted May 23, 2014 Author Report Posted May 23, 2014 the solution is ... i have created a function that takes 2 parameters... in the function, i am loading those 2 params into a table say it as table_min and then i am doing cross join on table_min and lookup table.... while doing the cross join i am applying my formula.... with this it gves me one to all rows calculation..... select "formula" from table_min cross join lookup_tbl so i call this function for each row....
Recommended Posts