Joining three tables such that extra matches are discarded?

Question

How can I write a query to give the results of three tables such that there's only one result per "line"?

The tables are:

T1 (ID, name, IP) 
T2 (ID, date_joined) 
T3 (ID, address, date_modified)

The relations are: T1-T2 1:1, T1-T3 1:M - there can be many address rows per ID in T3.

What I want is a listing of all users with the fields above, but IF they have an address, I only want to record ONE (bonus would be if it is the latest one based on T3.date_modified).

So I should end up with exactly the number of records in T1 (happens to be equal to T2 in this case) and no more.

I tried:

select t.ID, t.name, t.IP, tt.ID, tt.date_joined, ttt.ID, ttt.address 
from T1 t JOIN T2 tt ON (t.ID = tt.ID) JOIN T3 ttt ON (t.ID = ttt.ID)

And every sensible combination of LEFT, RIGHT, INNER, etc joins I could think of! I keep getting multiple duplicate because of T3

score 2 · Accepted Answer · answered Jul 02 '15 at 03:30

2

This query should work:

select 
    t1.ID, t1.name, t1.IP, t2.date_joined, t3x.address
from t1
join t2 on t1.ID = t2.id
left join (
    select t3.* 
    from t3 
    join (
       select id, max(date_modified) max_date 
       from t3 
       group by id
    ) max_t3 on t3.id = max_t3.id and t3.date_modified = max_t3.max_date
) t3x on t1.ID = t3x.id

First you do the normal join between t1 and t2 and then you left join with a derived table (t3x) that is the set of t3 rows having the latest date.

answered Jul 02 '15 at 03:30

jpw

44,361
6
66
86

Unless there are multiple rows with the same `date_modified`, but that's a bit of an edge case. – Blorgbeard Jul 02 '15 at 03:31
@Blorgbeard Indeed. You solution was much better anyway, I feel a bit ashamed tbh for overcomplicating it... – jpw Jul 02 '15 at 03:32
Mine has drawbacks too. A real join is usually more useful than a subquery, even if the code is a bit more complex :) – Blorgbeard Jul 02 '15 at 03:34
VERY edge case because date_modified goes down to the fractional second. This worked perfectly, thank you. I don't think I would have ever come up with an answer on my own. I shutter to even think how your brain works ;) – bcsteeve Jul 02 '15 at 07:10

Blorgbeard · Answer 2 · 2015-07-02T03:32:48.160

1

So T2 is actually not relevant here. You just need a way to join from T1 to T3 in a way that gets you at most one T3 row per T1 row.

One way of doing this would be:

select 
  T1.*, 
  (select address from T3 where T3.ID=T1.ID order by date_modified desc limit 1)
from T1;

This won't likely be very efficient, being a correlated subquery, but you may not care depending on the size of your dataset.

It's also only good for getting one column from T3, so if you had Address, City, and State, you'd have to figure out something else.

edited Jul 02 '15 at 03:32

answered Jul 02 '15 at 03:30

Blorgbeard

101,031
48
228
272

First, I don't see how T2 is irrelevant if I need date_joined from it? And I'm sorry for being incomplete with the example data. There is certainly more data to pull from T3. I was trying to simplify it for sake of question presentation (fail) – bcsteeve Jul 02 '15 at 06:56
Sorry, didn't notice that you needed the date from T2. Still, it's easy to simply `join T2` etc - that's not the hard part! – Blorgbeard Jul 02 '15 at 07:57

score 0 · Answer 3 · answered Jul 02 '15 at 03:32

You can use sub query with Top 1 so that u get only one result from T3

here is a sample sql

select * into #T1 from(
select 1 ID
union select 2
union select 3) A

select * into #T2 from(
select 1 ID
union select 2
union select 3) A

select * into #T3 from(
select 1 ID, 'ABC' Address, getDate() dateModified
union select 1, 'DEF', getDate()
union select 3, 'GHI', getDate()) A

select *, (select top 1 Address from #T3 T3 where T3.ID= T1.ID order by datemodified desc) from #T1 T1
inner join #T2 T2 on T1.ID = T2.ID

Bonus :- you can also add order by dateModified desc to get the latest address

Joining three tables such that extra matches are discarded?

3 Answers3