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

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/

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

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

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