#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    6
    Rep Power
    0

    Can I stop duplicates when using a UNION


    Hi,

    I've got 2 queries that are linked together by a union statement, however it creates duplicates, is there a command or something which I can use to remove duplicates?



    Cheers
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,140
    Rep Power
    4274
    UNION does remove duplicates

    or more specifically, UNION removes duplicate rows

    if you have duplicate column values, then we'll need to see more info on which other columns are involved and how you might choose values from those other columns to go along with the single values from the "duplicate" column

    rudy
    http://r937.com/
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    6
    Rep Power
    0
    Thanks,

    I've taken out some of the query code below, to just leave the colums that are in both queries.

    SELECT int_item_parents.item_ID AS project_ID, items.title AS project_title, .parent_item_ID AS cr_ID , items_1.title AS cr_title, items_1.project_ref as old_cr_reference, items_1.description AS description, items_1.lead_customer AS lead_cust, items_1.supplier_lead AS lead_BTex, items_1.comments AS notes, items_1.production_date AS production_date, items_1.sit_date AS system_test, items_1.cat_date AS cat, items_1.est_completion_date, items_1.mandays, items_1.costs_estimated
    FROM
    WHERE
    UNION(
    SELECT items.parent_ID AS project_ID, items.parent_ID AS project_title, items.ID AS cr_ID , items.title AS cr_title, items.project_ref as old_cr_reference, items.description AS description, items.lead_customer AS lead_cust, items.supplier_lead AS lead_BTex, items.comments AS notes, items.production_date AS production_date, items.sit_date AS system_test, items.cat_date AS cat, items.est_completion_date, items.mandays, items.costs_estimated
    FROM items
    ) ORDER BY project_ID DESC, cr_ID ASC";


    Now the problem arises as the first query picks out rows that have a project assigned to them. The second query just picks out all rows. Sounds simple, but the only way to work out if records have a project assigned is by checking a seperate table, which is where the first query is based. The duplicates can be identifed in CR_ID (highlighted in blue above) If you need any more help let me know
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,140
    Rep Power
    4274
    if your union produces rows like

    foo 1 2 a b c
    foo 1 2 - - -

    then UNION will never be able to remove those "duplicates" because they aren't duplicates

    look into LEFT OUTER JOIN instead


    rudy
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    6
    Rep Power
    0
    I had previously tried using an Inner Join, but I couldnt mange to get it to work.

    Can you briefly outline how I should try and arrange it so that I dont get the duplicates? I've had a look on web, but its mostly just how to structure the joins, not what the difference between them all is
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,140
    Rep Power
    4274

IMN logo majestic logo threadwatch logo seochat tools logo