CODEWITHSUNDEEP
sqlms-accessinner-joindistinct
naiminp
naiminp

Reputation: 181

Select Distinct rows from the joining of two SQL tables

I'm trying to determine the following from the joining of two tables: TableA: Contains 1 column consisting of unique IDs TableB: Contains multiple columns, one with consisting of the same set of unique IDs but also with a date column for each day of the year. For example:

Table A
Unique ID
1
2
3

Table B
UniqueID  Date    Column3  Column4
1        10/11/15 
2        10/11/15
1        11/11/15
2        11/11/15
3        11/11/15

I want the query to output the a table with the unique IDs and the first day on which they appear in table B. So for unique 1 and 2, to output only the first date, and for unique 3, only the second date.

Is this possible?

Thanks

Upvotes: 0

Views: 4372

Answers (3)

my779
my779

Reputation: 79

If Table A is your main table, there might be times where IDs in Table A aren't found on Table B yet, in that case, you will still want to output all values from Table A with corresponding dates (NULL if no match found):

SELECT A.UniqueID, MIN(DATE) as [Date] FROM [Table A] A
LEFT JOIN [Table_B] B ON A.Unique ID = B.UniqueID
GROUP BY A.UniqueID

Upvotes: 0

Tyler Roper
Tyler Roper

Reputation: 21672

The JOIN portion is unnecessary given your example above, but in the event that you actually only want records that are included in the first table, you could do an INNER JOIN like so.

SELECT tb.UniqueID, MIN(DATE) as [Date] FROM [Table B] tb
INNER JOIN [Table_A] ta ON ta.[Unique ID] = tb.UniqueID
GROUP BY tb.UniqueID

I've used your table and column names, despite the lack of naming consistency, and the fact that your tables and columns have spaces in them. (Personally, I'd advise against this in any SQL implementation, but that's a separate issue.)

Without the join you'd just need to do...

SELECT UniqueID, MIN(DATE) as [Date] FROM [Table B]
GROUP BY UniqueID

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269623

This is a group by:

select uniqueID, min(Date)
from b
group by uniqueID;

You don't even need an INNER JOIN, unless some ids are missing from b.

Upvotes: 1

Related Questions