Union Select Showing Duplicates

[tfaiers]

I've been trying to write and rewrite this SQL statement and I just can't get it right, I would like to use UNION to join two tables and return a list of entries excluding duplicates which works fine on its own if I only lookup the tetra_issi and radio_call_sign fields, but I also need to add a defined field to classify which table the information came from.

The SQL documentation seems to infer that you can add a defined field without affecting the data, but it's the addition of the defined field that seems to allow the duplicates. I've tried combining the UNION into another SELECT statement and using, MAX, TOP etc., but to no avail, thanks everyone.

I just want to end up with a complete sequence of tetra_issi numbers / radio_call_sign fields showing only the entries from the RADIOS_V11 which are not present in the CALLS_V4 table which should result as:

1001 User1 Logged On (from CALLS_V4 table)
1002 User2 Logged On (from CALLS_V4 table)
1003 User3 Logged Off (from RADIOS_V11 table)

but at the moment the results come in as:

1001 User1 Logged On (from CALLS_V4 table)
1002 User2 Logged On (from CALLS_V4 table)
1003 User3 Logged Off (from RADIOS_V11 table)
1003 User3 Logged On (from CALLS_V4 table)


Here's my last iteration of the SQL statement:

SELECT tetra_issi, radio_call_sign, current_status
FROM
(
SELECT tetra_issi, call_sign AS radio_call_sign, 'Logged Off' AS current_status
FROM RADIOS_V11
WHERE (tetra_issi >= '1000') AND (tetra_issi <= '1999')
UNION
SELECT tetra_issi, radio_call_sign, 'Logged On' AS current_status
FROM CALLS_V4
WHERE (tetra_issi >= '1000') AND (tetra_issi <= '1999') AND (status_text = 'Safety Logon' OR status_text = 'Radio PTT') AND (LEFT(call_time, 8) = @CURDATE)
)
AS temp_table
ORDER BY tetra_issi

 

[plog]

1001 User1 Logged On (from CALLS_V4 table)
1002 User2 Logged On (from CALLS_V4 table)
1003 User3 Logged Off (from RADIOS_V11 table)
1003 User3 Logged On (from CALLS_V4 table)

Those are not duplicates, each row is unique.

If the 'Logged Off' records are to take precedence over the 'Logged On' records, then you need to incorporate that logic into your second SELECT of the UNION:

SELECT tetra_issi, radio_call_sign, 'Logged On' AS current_status
FROM CALLS_V4
WHERE (tetra_issi >= '1000') AND (tetra_issi <= '1999') AND (status_text = 'Safety Logon' OR status_text = 'Radio PTT') AND (LEFT(call_time, 8) = @CURDATE)

That portion needs to get a lot more complex. For reference sake, call the above SQL 'sub2' and the query just before it in the UNION 'sub1'.

What you need to do is incorporate the SQL of sub1 in a LEFT JOIN in the SQL of sub2. Then you update sub2's WHERE clause to include only records where that are not in sub1. Again, all this happens in the SQL of sub2.

 

[tfaiers]

I'll try rewriting the statement tomorrow and let you know if I'm able to get the syntax correct, I'm unfortunately still learning much about SQL.

 

[plog]

The good thing about UNION queries is that you can take part of it out and run it by itself to debug it.