remove duplicate results when using union and innerjoin together??

[dotty]

Okay - here's what I've got:

3 tables - common key is 'id' table 3 holds all main data and tables 1 and 2 hold multiple options for main data - so I want to list the 'ids' in both relational (tables 1 and 2) and link up with main data in table 3

so my query looks like this:

SELECT table1.id from table_1, table3.dataA, table3.dataB from table3 INNERJOIN table3 ON table3.id = table1.id where etc..
UNION
SELECT table2.id from table_2 table3.dataA, table3.dataB from table3 INNERJOIN table3 ON table3.id = table2.id where ...etc
GROUP BY id

My query is producing the desired results but not removing duplicates - despite the fact that I've used UNION and GROUP BY - I've also tried DISTINCT - I've tried using all three, and each individually, but still get duplicates!

Does anyone know any duplicate removing magic?? I've added colours to the tables to try and make it easier to read 🙂

[tomhath]

The SQL should look something like this (can't tell from your post what you really ran). I don't see how this could return duplicates (of course the same id could appear multiple times, but with different values from table3):

SELECT DISTINCT table1.id, table3.dataA, table3.dataB  

from table_1 
INNER JOIN table3   

ON table3.id  = table1.id  

where etc..
UNION
SELECT DISTINCT table2.id, table3.dataA, table3.dataB 
from table_2  

INNER JOIN table3 
ON table3.id = table2.id 
where ...etc