Join on group id, to get missing individual ids in that table

Question

For the following data sets (super simplified):

t1

ext_id, tid, aid, aum, actions
z1,     1,   a,   100, 100
z2,     1,   b,   100, 100
x1,     2,   d,   200, 200
x2,     2,   e,   200, 200

t2

tid, aid, aum, actions
1,   a,   100, 100
1,   b,   100, 100
1,   c,   100, 100
2,   d,   200, 200
2,   e,   200, 200
2,   f,   200, 200

I would like to match t1 with t2, and get all data for the rest of the aid's, that join based on tid:

ie. output should be:

ext_id, tid, aid, aum, actions
(null), 1,   c,   100, 100
(null), 2,   f,   200, 200

I tried:

select (null) as ext_id, b.*
from #t1 a
right join #t2 b
on a.tid=b.tid
where a.aid is null

But it looks incorrect.

Thank you

@Y.S updated what I tried – user8834780 Feb 12 '18 at 17:19 — user8834780, Feb 12 '18 at 17:19

score 2 · Answer 1 · answered Feb 12 '18 at 17:07

2

Use an outer join and IS NULL operator:

select * from t2
left join t1
using (`tid`, `aid`, `aum`, `actions`)
where ext_id is null

demo: http://sqlfiddle.com/#!9/9935c7/4

answered Feb 12 '18 at 17:07

krokodilko

35,300
7
55
79

this is MySQL, I am using Redshift- are you sure this works? I dont see `using` anywhere in documentation – user8834780 Feb 12 '18 at 17:16
LEFT JOIN .... USING ... WHERE ... IS NULL all very basic features of SQL-Standard, almost all execpt some very exotic and limited databases support them, RedShit supports them too, here is an example in [the documentation](https://docs.aws.amazon.com/redshift/latest/dg/r_Join_examples.html) of LEFT JOIN. – krokodilko Feb 12 '18 at 17:24
Thank you. Unfortunately I have 30+ records (As mentioned, this is way simplified), so it wouldn't work for what I am trying to do as is. Any way to make this more scalable? – user8834780 Feb 12 '18 at 18:12

score 1 · Accepted Answer · answered Feb 13 '18 at 07:25

1

I think that in your case you can use not exists to get what you want.

I'm suggesting the following:

select *
from t2
where not exists (SELECT 1 FROM t1 where t1.tid = t2.tid and t1.aid = t2.aid)

answered Feb 13 '18 at 07:25

Y.S

1,860
3
17
30

@user8834780 I noticed you posted an answer which worked for you, but this one may be more intuitive. Either way, glad you got it resolved. – Y.S Feb 13 '18 at 12:51

score 0 · Answer 3 · answered Feb 12 '18 at 17:36

0

Try this:

 select '(Null)' as ext_id, t2.tid, t2.aid, t2.aum, t2.actions
 from t2 left join t1 
 on t2.tid=t1.tid 
 and t2.aid=t1.aid
 where t1.tid is null and t1.aid is null

answered Feb 12 '18 at 17:36

Murali Dhar Darshan

285
1
17

thank you but this returns records from `t2` based with `tid`s that dont exist in `t1`. I only need `aid`s from `t2` that are missing from `t1` based on only `tid`s that exist in `t1` – user8834780 Feb 12 '18 at 17:48

score 0 · Answer 4 · answered Feb 12 '18 at 18:17

0

This is the correct (scalable) answer that worked for me:

select distinct b.*
from t1 a
join t2 b on a.tid=b.tid
left join t1 c on b.aid=c.aid
where c.aid is null

answered Feb 12 '18 at 18:17

user8834780

1,620
3
21
48

score 0 · Answer 5 · answered Feb 12 '18 at 19:19

0

run the following query to get your desired result

select t1.ext_id,t2.tid,t2.aid,t2.aum,t2.actions from t1 
right join t2 
on  t1.aid= t2.aid 
where t1.ext_id is null

answered Feb 12 '18 at 19:19

Khan M

415
3
17

Thanks Khan, this is the same concept as krokodilko's answer, however this is not scalable if there are many other columns like in my case (there are actually 30+). – user8834780 Feb 12 '18 at 19:32

Join on group id, to get missing individual ids in that table

5 Answers5