locked
Identify each row uniquely RRS feed

  • Question

  • I have two tables A and B with different total record count..I am trying to join these two tables on deal_id and contract_month combination..but I am having trouble with joining..as some of the combinations have multiple records..

    e.g.

    process date deal id Contract Month Volume basis point
    3/30/2012 166690 201204 10 Others- (Ventura) GD
    3/30/2012 166690 201204 20,000 *Not Available
    3/30/2012 166690 201204 30,000 *Not Available
    3/30/2012 166690 201204 30,000 *Not Available
    process date deal id Contract Month Volume basis point
    3/29/2012 166690 201204 10 Others- (Ventura) GD
    3/29/2012 166690 201204 20,000 *Not Available
    3/29/2012 166690 201204 30,000 *Not Available
    3/29/2012 166690 201204 30,000 *Not Available

    I can only join on deal_id+contract_month..so each record of A ties with each record of B :(

    Is there any way to solve this problem?

    Thursday, July 12, 2012 9:19 AM

Answers

  • You *could* wrap each select inside a CTE and add 'ROW_NUMBER() over( Partition by [deal id],[Contract month],[Volume] order by (Select null)'

    then in the SELECT (FULL OUTER ???) join on row_number equality , does that help ?


    Dave Ballantyne ---- http://sqlblogcasts.com/blogs/sqlandthelike/

    • Marked as answer by rockstar283 Tuesday, July 17, 2012 2:28 AM
    Thursday, July 12, 2012 9:34 AM

All replies

  • I was not able to clearly understand your question. From the sample data your provide, I was wondering if the DISTINCT key word can solve your problem.

    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!

    Thursday, July 12, 2012 9:33 AM
  • You *could* wrap each select inside a CTE and add 'ROW_NUMBER() over( Partition by [deal id],[Contract month],[Volume] order by (Select null)'

    then in the SELECT (FULL OUTER ???) join on row_number equality , does that help ?


    Dave Ballantyne ---- http://sqlblogcasts.com/blogs/sqlandthelike/

    • Marked as answer by rockstar283 Tuesday, July 17, 2012 2:28 AM
    Thursday, July 12, 2012 9:34 AM
  • What is desired result? 

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Thursday, July 12, 2012 9:37 AM
    Answerer
  • select t1.*, t2.*

    from table1 t1, table2 t2

    where t1.[deal id] = t2.[deal id] and

     t1.[Contract Month] = t2.[Contract Month]

    Try to use like this way.


    Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!


    • Edited by RohitGarg Thursday, July 12, 2012 9:41 AM
    Thursday, July 12, 2012 9:41 AM
  • RohitGarg

    Why old ANSI-89 standard ? Why not by JOIN on? 


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Thursday, July 12, 2012 9:47 AM
    Answerer
  • i assuem you wish to combine both the tables into one resultset and you want some columns from both tables to appear only once. looking at the tabular structure, you can UNION both tables and then group by with desired columns or ALL columns.

    select * from 
    (
    SELECT * from table1
    union 
    select * from table2
    ) A
    group by A.process_date, A.deal_id, A.Contract_Month, A.Volume, A.basis_point

    regards

    joon

    Thursday, July 12, 2012 9:50 AM
  • If you are looking forward to JOIN these two tables on deal_id+contract_month and combination of these columns have multiple records in either of the tables, there is no way to JOIN them ONE ON ONE.

    You have to have some uniqueness defined for each row in both the tables to join them as per your need.


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Thursday, July 12, 2012 10:00 AM
  • thank you all for replying..sorry for not being entirely clear earlier..all i want to do is to join two tables on deal id and contract month and get the matching resulta side by side..so tyat i can match each field of A with each field of B to understand what changed between A and B
    Thursday, July 12, 2012 10:09 AM
  • My bad Uri.....Join ON is a better option


    Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    Thursday, July 12, 2012 10:40 AM
  • Hey rockstar!  I'm not sure what your final result is supposed to look like, but try to do what Rohit recommended, and after you do that, take a look at this:

    http://blog.sqlauthority.com/2007/03/29/sql-server-difference-between-distinct-and-group-by-distinct-vs-group-by/

    You may need another query to Group records, or use Select Distinct, from the Query that Rohit suggested.


    Ryan Shuell

    Friday, July 13, 2012 8:54 PM
  • Maybe I'm a bit stupid, but do those tables have a primary key? If you want to join them on deal_id and Contract_Month and if those columns are not unique, as your sample data and your remark suggests, and if all the other fields can vary (which seems the case, since you want to track the differences), I don't see how it can be done. I even dare to state that it is theoretically impossible.
    Friday, July 13, 2012 9:12 PM
  • first select only distinct values before joining with other table. to avoid duplicates.

    SQL Champ
    Database Consultants NY

    Tuesday, July 17, 2012 2:03 AM