Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    223

    Unanswered: How to return an unique from two table with duplicated records?

    Hello, everyone:

    I have two tables with some duplicated records like,

    ZZZTest:
    C_ID C1 C2 C3
    10 A A A
    20 B B B
    30 A A A
    40 B B B


    ZZZTestTable:
    D_ID C11 C22 C33
    1 A A A
    2 A A A
    3 B B B
    4 B B B
    5 A A A
    6 A A A
    7 B B B


    I wand to get the unique records by SELECT / JOIN statement. Now I used a query,

    SELECT * FROM ZZZTest t
    INNER JOIN ZZZTestTable tt
    ON t.Col1=tt.Col11 AND t.Col2=tt.Col22 AND t.Col3 = tt.Col33

    and got the records like,
    C_ID C1 C2 C3 D_ID C11 C22 C33
    10 A A A 1 A A A
    30 A A A 1 A A A
    10 A A A 2 A A A
    30 A A A 2 A A A
    20 B B B 3 B B B
    40 B B B 3 B B B
    20 B B B 4 B B B
    40 B B B 4 B B B
    10 A A A 5 A A A
    30 A A A 5 A A A
    10 A A A 6 A A A
    30 A A A 6 A A A
    20 B B B 7 B B B
    40 B B B 7 B B B



    What I am expecting is,
    C_ID C1 C2 C3 D_ID C11 C22 C33
    10 A A A 1 A A A
    30 A A A 2 A A A
    20 B B B 3 B B B
    40 B B B 4 B B B

    Any suggestion will be great appreciated.

    Thanks

    ZYT

  2. #2
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Is this a trick question or is just the fact that you have no way of joining table1 to table2

    possibly because the schema designer does'nt know what he is doing

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  3. #3
    Join Date
    Mar 2003
    Posts
    223
    I am looking for a way by which I can retriev not duplicated C_ID and D_ID in results.

  4. #4
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    yit

    and got the records like,
    C_ID C1 C2 C3 D_ID C11 C22 C33
    10 A A A 1 A A A
    30 A A A 1 A A A
    10 A A A 2 A A A
    30 A A A 2 A A A
    20 B B B 3 B B B
    40 B B B 3 B B B
    20 B B B 4 B B B
    40 B B B 4 B B B
    10 A A A 5 A A A
    30 A A A 5 A A A
    10 A A A 6 A A A
    30 A A A 6 A A A
    20 B B B 7 B B B
    40 B B B 7 B B B
    achieves what you have just asked for ?
    not duplicated C_ID and D_ID in results
    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

Posting Permissions

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