Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2014
    Posts
    3

    Unanswered: Querry for matching data

    hi,
    I have two tables like below,I need to check whether table1 data is matching with table2 data based on supplier.Here problem is
    in table1 we have two suppliers ebay and amazon and it's ids are 1 and 2 for other suppliers it should be anything.In table2
    for ebay and amazon and it's ids are 11 and 22.By using inner join i can match these data but for ebay and amazon id's are differnet
    in table2.I need to check these two tables when in table1 ebay and amazon suppliers ids are 1 and 2 and in table2 ebay and amazon suppliers ids are 11 and 22
    as per business logic and for remaining suppliers wat ever ids are present in table1 should match with table2 suppliers wat ever ids are present except ebay and amzon
    .for ebay and amzon ids are 11 and 22.any one can help me on this querry.

    table1:

    Supplier id value
    ebay 1 4
    amazon 2 5
    aa 3 6

    table2:

    Supplier id value
    ebay 11 4
    amazon 22 5
    aa 3 6

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The first thing to do would be to normalize the database and have a unique ID for each supplier.
    Have a nice day!

  3. #3
    Join Date
    Mar 2014
    Posts
    3
    supplier is unique here.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its all down to the where clause (well truth be told its all down to crap data and dodgy table design)

    a supplier is a supplier, and you should use the internal referenece number. if you must have multiple external referneces then you need to create that relationshoip in a table

    fix your table design and these issues go away.
    don't have two tables with what is, seemingly the same data

    ebay isn't a supplier its a sales platform. it doesnt' seel things, but it does sell services to vendors
    Amazon can be a supplier but its also a sales platform. where aother business use Amazon to either advertise their goods or act as a fulfillment agent

    getting a workaround for your current problem should be trivial, instead of a join use a theta join, although deprecated it still works

    it should be somethign like
    Code:
    where (table1.id = 1 and table2.id = 11)
     or (table1.id = 2 and table2.id = 22)
     or (table1.id=table2.id AND table1.id not in (1,2) AND table2.id not in(11,22))
    but you'd better check the logic

    in essence
    you want to join t1 to t2 where ID in t1 is 1 and T2 is 11
    OR
    you want to join t1 to t2 where ID in t1 is 2 and T2 is 22
    OR
    where the IDs of T1 & t2 match AND T1 id is not 1 or 2 AND T2 Id is not 11 or 22

    frankly its a crap design that makes the where clause needlessly complex
    Last edited by healdem; 04-03-14 at 05:55.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by toravietl View Post
    supplier is unique here.
    no its not
    you have two tables holding similar data, where you are duplicating suppliers

    have one table for suppliers
    and if you must persist with your different referneces then have other tabels that associate a supplier with what ever other codign you need.

    eg

    suppliers
    999 fleabay
    666 taxevader
    123 another

    t1supplier
    1 999
    2 666

    t2supplier
    11 999
    22 666

    ... but you could roll up those tables into one
    supplierXref
    1 999
    11 999
    2 666
    22 666
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •