Union Query showing duplicates

[scoe]

Hi, I am desparate for some help on this please, I am getting dupicate [Student_id] and [Student], I have managed to remove some with the where clause but not all. The code I have is below, please can some one tell me what is wrong with it?

[SELECT DISTINCT FAMLSR16A.student_id, FAMLSR16A.Student, FAMLSR16A.learn_sup_1a AS learn_sup_1
FROM FAMLSR16A
WHERE FAMLSR16A.learn_sup_1a is not null
UNION SELECT DISTINCT FAMLSR16A.student_id, FAMLSR16A.Student, FAMLSR16A.Expr1 AS learn_sup_1
FROM FAMLSR16A


WHERE FAMLSR16A.Expr1 is not null;]

Thank you

[JoeM]

Are ALL the fields being duplicated, or just the Student_id and Student fields?
If just those two, then those records are not true duplicates. A true duplicate means ALL the fields are the same as another record.

[scoe]

Hi
Yes, it is just those two fields, I have a screen shot below

Student ID	Student	learn_sup_1	learn_sup_2
SJ125862	Sophie Jones	56
SJ125862	Sophie Jones	56	59

[JoeM]

So, based on your example, what do you want (expect) to see?

[scoe]

I only want to see

SJ125862

Sophie Jones

56

59

[JoeM]

I am guessing that of those two original records, one is coming from the first query, and one from the second?
Perhaps you can do an Aggregate Query, and take the Max value of each of the last two fields.
You might need to do it in a second, separate query based on your first one (or nest the queries, if you know how).

[lfpm062010]

This is your original SQL statement:

SELECT DISTINCT FAMLSR16A.student_id, FAMLSR16A.Student, FAMLSR16A.learn_sup_1a AS learn_sup_1
FROM FAMLSR16A
WHERE FAMLSR16A.learn_sup_1a is not null
UNION SELECT DISTINCT FAMLSR16A.student_id, FAMLSR16A.Student, FAMLSR16A.Expr1 AS learn_sup_1
FROM FAMLSR16A
WHERE FAMLSR16A.Expr1 is not null;

But your output show an extra column "learn_sup_2".

So, if you change your SQL to exclude rows when "learn_sup_2" is null. Then you will get your result. Base on your current input.