Nested Join in Subquery and failing correlation

Question

I have 3 tables sc_user, sc_cube, sc_cube_sent

I wand to join to a user query ( sc_user) one distinct random message/cube ( from sc_cube ), that has not been sent to that user before ( sc_cube_sent), so each row in the result set has a disctinct user id and a random cubeid from sc_cube that is not part of sc_cube_sent with that user id associated there.

I am facing the problem that I seem not to be able to use a correlation id for the case that I need the u.id of the outer query in the inner On clause. I would need the commented section to make it work.

# get one random idcube per user not already sent to that user
SELECT u.id, sub.idcube
FROM sc_user as u
LEFT JOIN (
    SELECT c.idcube, sent.idreceiver FROM sc_cube c
    LEFT JOIN sc_cube_sent sent ON ( c.idcube = sent.idcube /* AND sent.idreceiver = u.id <-- "unknown column u.id in on clause" */  )
WHERE sent.idcube IS NULL
ORDER BY RAND()
LIMIT 1
) as sub
ON 1

I added a fiddle with some data : http://sqlfiddle.com/#!9/7b0bc/1 new cubeids ( sc_cube ) that should show for user 1 are the following : 2150, 2151, 2152, 2153

Edit>>

I could do it with another subquery instead of a join, but that has a huge performance impact and is not feasible ( 30 secs+ on couple of thousand rows on each table with reasonably implemented keys ), so I am still looking for a way to use the solution with JOIN.

SELECT
u.id,
(SELECT sc_cube.idcube
FROM sc_cube
WHERE NOT EXISTS(
      SELECT sc_cube.idcube FROM sc_cube_sent WHERE sc_cube_sent.idcube = sc_cube.idcube AND sc_cube_sent.idreceiver = u.id
)
ORDER BY RAND() LIMIT 0,1
) as idcube
FROM sc_user u

can you add some data or a sql fiddle? also you are specifying where the idcube is null so your on clause can't be based by idcube — John Ruddell, Jun 04 '15 at 18:14
do you want all of those results for user1? i thought you were ordering by rand and limiting 1 — John Ruddell, Jun 04 '15 at 19:10
oh ok gotcha... so there is an issue with your initial query... there are no idreceivers that get returned... http://sqlfiddle.com/#!9/8d1df/5 — John Ruddell, Jun 04 '15 at 20:30
Thanks John, but I don't need idreceiver and you are checking against 2 known ids i want the idcube being related to each user row in the result set. — XtraBytesLab, Jun 04 '15 at 22:07
i know it is two known id's... the query is identical to what you wanted i figured i would start with the known id's since its trying to use u.id.. what im saying is the query you have doesn't return any recieverid so i dont know if that is correct. — John Ruddell, Jun 04 '15 at 22:15
of course we dont have idreceivers because we are asking for the idcubes that have not been sent for that user, hence have no idreceiver. — XtraBytesLab, Jun 04 '15 at 22:16
ok, so what is the point of that AND part on your LEFT JOIN ? if there isn't a match then it has no use — John Ruddell, Jun 04 '15 at 22:20
The use is as in the description : I wand to join to a user query ( sc_user) one distinct random message/cube ( from sc_cube ), that has not been sent to that user before ( sc_cube_sent), hence the id of the cube taking from the sc_cube, but cant be already in sc_cube_sent — XtraBytesLab, Jun 04 '15 at 22:25
so currently it is returning a random one that the user doesn't have.. is the issue the fact that its the same one per user? — John Ruddell, Jun 04 '15 at 23:03
The problem with that is, its an id that is not in sc_cube_sent in general, no user relation is made at that point. — XtraBytesLab, Jun 04 '15 at 23:34

score 0 · Answer 1 · answered Jun 04 '15 at 18:20

0

without being able to test this, I would say you need to include your sc_user in the subquery because you have lost the scope

LEFT JOIN 
(   SELECT c.idcube, sent.idreceiver 
    FROM sc_user u
    JOIN sc_cube c ON c.whatever_your_join_column_is = u.whatever_your_join_column_is
    LEFT JOIN sc_cube_sent sent ON ( c.idcube = sent.idcube AND sent.idreceiver = u.id  )
    WHERE sent.idcube IS NULL
    ORDER BY RAND()
    LIMIT 1
) sub

answered Jun 04 '15 at 18:20

John Ruddell

25,283
6
57
86

Thanks John! Problem is with loosing the sc_user scope is that the query is a excerpt of a larger query where u is part of the very outer scope. I will setup a fiddle with some data. – XtraBytesLab Jun 04 '15 at 18:36

krokodilko · Answer 2 · 2015-06-04T19:20:20.000

0

If you want to get messagges ids that has not been sent to the particular user, then why use a join or left join at all ?
Just do:

SELECT sent.idcube 
FROM sc_cube_sent sent 
WHERE sent.idreceiver <> u.id

Then the query may look like this:

SELECT u.id, 
       /* sub.idcube */
      (  SELECT sent.idcube 
         FROM sc_cube_sent sent 
         WHERE sent.idreceiver <> u.id
         ORDER BY RAND()
         LIMIT 1
      ) as idcube
FROM sc_user as u

edited Jun 04 '15 at 19:20

answered Jun 04 '15 at 18:43

krokodilko

35,300
7
55
79

I think you don't need LEFT JOIN at all, please take a look at the answer for details. – krokodilko Jun 04 '15 at 19:22
because the id I need is not or might not be ( as potentially sent to other users ) in sc_cube_sent, but sc_cube. only sent ids land in sc_cube_sent, hence the join. And I need one random cube id per user ( to spread the ids across ) – XtraBytesLab Jun 04 '15 at 19:32
Thanks kordirko, I modified that approach ( see description edit ), but it's slow as hell that way ( 30+ seconds vs 0.04 seconds in the solution with JOIN ... but without the user correlation ) – XtraBytesLab Jun 05 '15 at 00:02

XtraBytesLab · Accepted Answer · 2015-06-05T13:06:11.457

0

Got it working with NOT IN subselect in the on clause. Whereas the correlation link u.id is not given within the LEFT JOIN scope, it is for the scope of the ON clause. Here is how it works:

SELECT u.id, sub.idcube
FROM sc_user as u
LEFT JOIN ( 
     SELECT idcube FROM sc_cube c ORDER BY RAND()
) sub ON ( 
  sub.idcube NOT IN ( 
     SELECT s.idcube FROM sc_cube_sent s WHERE s.idreceiver = u.id
  ) 
)
GROUP BY u.id

Fiddle : http://sqlfiddle.com/#!9/7b0bc/48

edited Jun 05 '15 at 13:06

answered Jun 05 '15 at 12:36

XtraBytesLab

626
7
11

If you are curious as to why you were getting that error, it has to to specifically with your original use of the ON clause, if you have moved the check that was causing the error to the WHERE clause of the same subquery, you would have avoided the error message. See my question here: http://stackoverflow.com/questions/35788185/difference-between-on-and-where-in-subquery – Omn Mar 04 '16 at 04:48
Nm, I lied. It works in my case, but not in this case: http://sqlfiddle.com/#!9/7b0bc/49 – Omn Mar 04 '16 at 05:02

Nested Join in Subquery and failing correlation

3 Answers3