October 9th, 2003, 07:04 AM
Can I stop duplicates when using a UNION
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?
October 9th, 2003, 07:16 AM
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
October 9th, 2003, 07:22 AM
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
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
) 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
October 9th, 2003, 07:27 AM
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
October 9th, 2003, 07:41 AM
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
October 9th, 2003, 07:53 AM