sql - MySQL - Join inside a correlated subquery -

i'm writing query assign users , respective domains ip addresses. no ip address may have duplicate users.

here's i've got far in sql fiddle: http://sqlfiddle.com/#!2/39c51/2/0

i have table contains (hundreds of thousands of) current assignments. example on smaller scale following:

mysql> select * test.usermap; +-------------+-------+-------------------+ | vip         | user  | domain            | +-------------+-------+-------------------+ | 100.50.20.1 | joe   | joesdomain.com    | | 100.50.20.1 | bob   | joesdomain.com    | | 100.50.20.2 | tom   | domain2.com       | | 100.50.20.2 | fred  | domain2.com       | | 100.50.20.2 | sally | domain2.com       | | 100.50.20.3 | admin | athriddomain.com  | | 100.50.20.4 | admin | numberfour.com    | | 100.50.20.3 | sally | fivewithsally.com | | 100.50.20.4 | jim   | thesix.com        | | 100.50.20.1 | admin | seven.com         | | 100.50.20.1 | sally | seven.com         | | 100.50.20.1 | sue   | seven.com         | | 100.50.20.5 |       |                   | | 100.50.20.6 |       |                   | +-------------+-------+-------------------+ 14 rows in set (0.00 sec) 

i have table, contains yet-to-be assigned users, again, small scale example:

mysql> select * test.newusers; +-------+-----------+ | user  | domain    | +-------+-----------+ | jim   | eight.com | | sally | eight.com | | admin | nine.com  | | james | ten.com   | | jane  | ten.com   | +-------+-----------+ 5 rows in set (0.00 sec) 

the idea here assign users under eight.com .5 because thats earliest ip has neither 'jim' nor 'sally', , nine.com .2 , ten.com .1 because of respective user conflicts (or lack thereof).

the result i'm looking like:

+-------------+-------+-----------+ | vip         | user  | domain    | +-------------+-------+-----------+ | 100.50.20.1 | james | ten.com   | | 100.50.20.1 | jane  | ten.com   | | 100.50.20.2 | admin | nine.com  | | 100.50.20.5 | jim   | eight.com | | 100.50.20.5 | sally | eight.com | +-------------+-------+-----------+ 5 rows in set (0.01 sec) 

i can subquery inside correlated subquery, so:

mysql> select   (     select vip      test.usermap     vip not in     (         select distinct vip          test.usermap           user in         (             select user              test.newusers              domain = n.domain         )     )     order inet_aton(vip) asc     limit 1 ) vip, n.user, n.domain  test.newusers n order inet_aton(vip) asc; +-------------+-------+-----------+ | vip         | user  | domain    | +-------------+-------+-----------+ | 100.50.20.1 | james | ten.com   | | 100.50.20.1 | jane  | ten.com   | | 100.50.20.2 | admin | nine.com  | | 100.50.20.5 | jim   | eight.com | | 100.50.20.5 | sally | eight.com | +-------------+-------+-----------+ 5 rows in set (0.00 sec) 

but horrendously inefficient, , production mapping , newusers tables 300k , 50k rows respectively, out of question.

i'm trying make more efficient using joins instead of nested subqueries, replaced inner query join , listed outer query's columns in on clause, seems isn't possible:

mysql> select  (     select distinct vip      test.usermap u      join test.newusers r         on r.domain = n.domain         , r.user != u.user     order inet_aton(vip) asc limit 1 ) vip, n.user, n.domain test.newusers n; error 1054 (42s22): unknown column 'n.domain' in 'on clause' mysql>  

while logic of query makes sense, because replacing outer query reference string constant represent works fine:

mysql> select (     select distinct vip      test.usermap u      join test.newusers r         on r.domain = 'ten.com'         , r.user != u.user     order inet_aton(vip) asc limit 1 ) vip, n.user, n.domain test.newusers n domain = 'ten.com'; +-------------+-------+---------+ | vip         | user  | domain  | +-------------+-------+---------+ | 100.50.20.1 | james | ten.com | | 100.50.20.1 | jane  | ten.com | +-------------+-------+---------+ 2 rows in set (0.00 sec) 

my question is: there way reference column outer query inside of join on inner query? if not, kind of (if any) alternatives exist without nesting subqueries in inefficient manner?

again, have fiddle here: http://sqlfiddle.com/#!2/39c51/2/0

i not sure how much, if any, more efficient be, query can rewritten without nesting multiple subqueries:

select  inet_ntoa(min(inet_aton(usermap.vip))) vip,         newusers.user,          newusers.domain    newusers         cross join usermap         left join         (   select  u.domain, m.vip                newusers u                     inner join usermap m                         on u.user = m.user         ) ex             on ex.domain = newusers.domain             , ex.vip = usermap.vip   ex.domain null group newusers.user, newusers.domain order vip asc;    

example on sql fiddle

addendum

the query above not return rows there no vip available, e.g. if 100.50.20.5 , 100.50.20.1 removed usermap query return:

vip             user    domain null            jim     eight.com null            sally   eight.com 100.50.20.1     james   ten.com 100.50.20.1     jane    ten.com 100.50.20.2     admin   nine.com 

whereas query have written return rows vip not null:

vip             user    domain 100.50.20.1     james   ten.com 100.50.20.1     jane    ten.com 100.50.20.2     admin   nine.com 

to around can use union:

select  inet_ntoa(min(inet_aton(a.vip))) vip,         a.user,          a.domain    (   select  usermap.vip,                     newusers.user,                      newusers.domain                newusers                     cross join usermap                     left join                     (   select  u.domain, m.vip                            newusers u                                 inner join usermap m                                     on u.user = m.user                     ) ex                         on ex.domain = newusers.domain                         , ex.vip = usermap.vip               ex.domain null             union             select  null vip,                     newusers.user,                     newusers.domain                newusers         ) group a.user, a.domain order vip asc; 

revised example on sql fiddle

i not sure logic handling cases there no vip available can't suggest solution part. can next vip using this:

select  inet_ntoa(max(inet_aton(usermap.vip)) + 1) nextvip    usermap 

another issue problem collisions in newusers, e.g. if newusers table contained these records:

('jim','eight.com'), ('sally','eight.com'), ('jim','eleven.com'), ('sally','eleven.com'); 

both query , mine allocate of these vip 100.50.20.5. if happen think best way around insert user names 1 domain @ 1 time. can done using joins:

to simplify query have created 2 views

create view usedvip     select  u.domain, m.vip        newusers u             inner join usermap m                 on u.user = m.user;  create view newusermap      select  usermap.vip,             newusers.user,              newusers.domain        newusers             cross join usermap             left join usedvip ex                 on ex.domain = newusers.domain                 , ex.vip = usermap.vip       ex.domain null; 

and final query is:

select  inet_ntoa(min(inet_aton(a.vip))) vip,         a.user,          a.domain    newusermap         left join newusermap b             on a.user = b.user             , a.vip = b.vip             , a.domain > b.domain         left join newusermap c             on a.user = c.user             , b.domain = c.domain             , b.vip < c.vip   c.user null group a.user, a.domain order vip asc; 

which returns:

vip             user    domain 100.50.20.1     jane    ten.com 100.50.20.1     james   ten.com 100.50.20.2     admin   nine.com 100.50.20.5     sally   eight.com 100.50.20.5     jim     eight.com 100.50.20.6     jim     eleven.com 100.50.20.6     sally   eleven.com 

example on sql fiddle