Is there a DISTINCT UNION QRY? 1

[rccline]

Is there a way to do a distinct union query?

I've tried this, but I still get duplicates:

SELECT DISTINCT tblName.FieldName
FROM tblName
UNION ALL SELECT tblName2.FieldName
FROM tblName2;

Thanks.
Robert

 

[PHV]

And this ?
SELECT tblName.FieldName
FROM tblName
UNION SELECT tblName2.FieldName
FROM tblName2;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rccline]

Thanks PHV:

Here is what I am trying to do.
I want a list of data from tblName1 and tblName2 which are unique No duplicates in the list that fomes form Fieldname.

fieldname can be apple whether it is in tblName1 or tblName2.

How do you eliminate duplicates?

tblName1 tblName2
Field1 Field2
Apples Apples
Oranges Apples
Apples Grapes
Apples Oranges


How do you design a SQL query that lists:

Apples
Oranges
Grapes

Thanks.

Robert

 

[PHV]

Have you tried my suggestion ?
With your new schema:
SELECT Field1 FROM tblName1
UNION SELECT Field2 FROM tblName2;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rccline]

PH:

I did try it. I have multiple apples in field1 in tblName1 and I have multiples Apples in field2 from tblName2. When I run the query, it lists duplicates of apples.

 

[PHV]

it lists duplicates of apples
But no duplicates of Oranges ?
So, you must have some space or non visible characters issue:
SELECT Trim(Field1) AS myField FROM tblName1
UNION SELECT Trim(Field2) FROM tblName2;

BTW, you don't use the ALL keyword, did you ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rccline]

PH: You wizard! You were right!

Many thanks.

Robert

 

[rccline]

PS: No. I didn't use ALL. It was spaces and spelling.

 

[rccline]

PS: No. I didn't use ALL. It was spaces AND spelling.