Unique ID's in Two fields of table. (1 Viewer)

[triplell89]

Hi,

First I would like to say that I know that SQL is very powerful, so this operation should be possible.

I have a table containing two ID fields: Sender, Receiver. How can I view the unique values of both fields?

The fields have the following rules:
1. 1 sender can have multiple receivers
2. 1 receiver can have multiple senders
3. A sender can also be a receiver, but not always.
4. A receiver can also be a sender, but not always.

So pretty much I have two lists, R and S. I want to combine the list, and then eliminate all the duplicates.

 

[georgedwilkinson]

Look up the "distinct" and "group by" topics in help.

 

[triplell89]

Look up the "distinct" and "group by" topics in help.

I was toying around with those, but I am unable to get the information into 1 list form. Also, it doesn't seem to work correctly

 

[georgedwilkinson]

Actually, it works great. Post what you have so far and let's take a look.

 

[triplell89]

SELECT DISTINCT SNDR_TC, RCVR_TC
FROM MTRC_MSG_DTL
GROUP BY SNDR_TC, RCVR_TC

I want the result to be 1 column.

Like I said, imagine I have two lists. I want to combine the two lists, and remove the duplicates from the list.

 

[triplell89]

SELECT DISTINCT SNDR_TC, RCVR_TC
FROM MTRC_MSG_DTL
GROUP BY SNDR_TC, RCVR_TC

I want the result to be 1 column.

Like I said, imagine I have two lists. I want to combine the two lists, and remove the duplicates from the list.

This is kind of weird, but I tried this:

SELECT DISTINCT SNDR_TC FROM MTRC_MSG_DTL
UNION
SELECT DISTINCT RCVR_TC FROM MTRC_MSG_DTL

I got a list back, like I wanted, however it seems like some of the information on it isn't in the table.

For example, I have ID #ACU2402, but I can't find it in the data.

 

[georgedwilkinson]

Keep looking. If it comes up in that query, it is in that table. Make sure you don't have the table filtered.

 

[boblarson]

Fastest way to find it is to open the table, make sure the filters are off, and then right click in the field you want to check and type or paste in the number in the FILTER FOR that pops up. Type or paste in that box in the right-click menu that pops up and then hit enter. If it is in there it will filter for that one and if not it will not show any records. Then do the same on the other field in question (after removing the filter of course).