CODEWITHSUNDEEP
mysqlsqljoin
bcsteeve
bcsteeve

Reputation: 1001

Joining three tables such that extra matches are discarded?

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

Upvotes: 0

Views: 35

Answers (3)

Blorgbeard
Blorgbeard

Reputation: 103467

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.

Upvotes: 1

Vineeth Vijayan
Vineeth Vijayan

Reputation: 1329

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

Upvotes: 0

jpw
jpw

Reputation: 44881

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.

Upvotes: 2

Related Questions