Post reply

Union query problem

  • October 20, 2004 at 11:43 am

    #60465

    I am trying to join two tables with a union and i want it to return only

    one occurence of the  data entries that match identically on 2 of the fields.

    If I have two tables with their id fields as below :

    table 1 fields        table2 fields

    ID1 ID2           ID1          ID2

    1     1                1           1

    2     1                2           2  

    3     3                4           1

    4     2                5           1

    8     6                 8          8

                 

    I am trying to get the following result:

     

    union table:

    ID1     ID2

    1         1

    2         1

    2         2

    3         3

    4         1

    4         2

    5         1

    8         6

     

    Any ideas of how I could do this ?

    Thanks in advance

  • October 20, 2004 at 12:38 pm

    #527352

    hey thrivani

    i did not understand u!.what happened to 8 8 in the second table.its not there in the output.

    usually a simple union operator would do the job for u .....thats what i presume

    jus try

    select * from table1 union select * from table2

    tell me if iam wrong....and explain a bit clearly.so that if i can...i will help u out

    Rajiv.

  • October 20, 2004 at 1:29 pm

    #527360

    select * from table 1 union select * from table 2 should do it.  If you wish to have all rows from both tables returned (in your example, row 1, 1 would be returned twice) use UNION ALL

    Gordon Pollokoff

    "Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones

  • October 21, 2004 at 3:34 am

    #527422

    As has been stated, a plain UNION will eliminate duplicates automatically (a fact which has tripped me up more than once!). The BOL documentation is to my mind a bit poor here - you have the section on the UNION operator in the SELECT topic, then right at the endof the UNION topic it says:

    ALL

    Incorporates all rows into the results, including duplicates. If not specified, duplicate rows are removed.

    Note: My emphasis! I would have thought that this default behaviour should be mentioned earlier but that's just my opinion

     

  • October 21, 2004 at 6:28 am

    #527437

    Thanku for your responses. The tables have  more than two fields in them and the rows will not be exact duplicates in the two tables but they will have exactly the same combination of fiield1 and field2 only. These are the rows that I need to appear only once in my final table. Hence a simple UNION does not work for me.

    Any ideas on this ?

     

  • October 21, 2004 at 6:37 am

    #527440

    Couldn't you simply do this? :

    select Id1, Id2 from table1

    Union

    select Id1, Id2 from table2

  • October 21, 2004 at 7:17 am

    #527453

    If I understand correctly and if both tables have the same number and type of columns then this

    SELECT ID1, ID2, col1, col2, colx

    FROM [table1]

    UNION

    SELECT ID1, ID2, col1, col2, colx

    FROM [table2]

    WHERE NOT EXISTS (SELECT 1 FROM [table1]

        WHERE [table1].ID1 = [table2].ID1

        AND [table1].ID2 = [table2].ID2)

    will extract all the rows from table1 and only the rows from table2 where ID1 & ID2 are not present in table1

    Far away is close at hand in the images of elsewhere.
    Anon.

  • October 21, 2004 at 9:03 am

    #527476

    Try this SQL, it start with "-- Note". I believe it is what you are looking for. 

      

    -- Note: The sequence of data columns selected must be of the same data type

    --Get data from Table1 where Table1 keys match Table2

    SELECT T1.ID1, T1.ID2, T1.col1, T1.col2, T1.colx

    FROM table1 T1, table2 T2

     WHERE T1.ID1 = T2.ID1

     AND T1.ID2 = T2.ID2

    -- Combine results and eliminated duplicate rows

    -- (duplicate - Every column value in row matches prior row)

    UNION

    --Get data from Table2 where Table2 keys match Table1

    SELECT T2.ID1, T2.ID2, T2.col3, T2.col4, T2.coly

    FROM table1 T1, table2 T2

     WHERE T1.ID1 = T2.ID1

     AND T1.ID2 = T2.ID2

    --- END SQL

     

    here is a sample:

    -- Note: The sequence of data columns selected must be of the same datatype

    --Get data from Table1 where keys match Table2

    SELECT T1.Plan_ID, T1.Plan_Type_ID, T1.Plan_Name TextValue

    FROM db_MARKTEST..EMSPlan T1, db_OCTFIVE_test..EMSPlan T2

     WHERE T1.Plan_ID = T2.Plan_ID

     AND T1.Plan_Type_ID = T2.Plan_Type_ID

    -- Combine results and eliminated duplicate rows

    UNION

    --Get data from Table2 where keys match Table1

    SELECT T2.Plan_ID, T2.Plan_Type_ID, T2.Short_Coverage_Description

    FROM db_MARKTEST..EMSPlan T1, db_OCTFIVE_test..EMSPlan T2

     WHERE T1.Plan_ID = T2.Plan_ID

     AND T1.Plan_Type_ID = T2.Plan_Type_ID

  • October 21, 2004 at 3:40 pm

    #527549

    Assuming you're content with keeping the table1 rows where there is a table2 duplicate, exclude the matching records from the second part of the union:

    select * from table1

    union

    select * from table2 t2

    where not exists 

      (select null from table1 t1 where t1.id1 = t2.id1 and t1.id2 = t2.id2)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply