Referencing Outer Query's Tables in a Subquery

Referencing outer query's tables in a subquery

i think that won't work, because you're referencing your derived table 'c' as part of a join.

however, you could just take out the WHERE p.user = u.id though and replace with a GROUP BY p.user in the derived table, because the ON c.user = u.id will have the same effect.

Why am I able to reference an outer queries columns from within a subquery?

When you have multiple tables in a query, always use qualified table names. You think the query is doing:

SELECT t1.col_a
FROM test_1 t1
WHERE t1.col_a IN (SELECT t2.col_a FROM test_2 t2);

This would generate an error, because t2.col_a does not exist.

However, the scoping rules for subqueries say that if the column is not in the subquery, look in the outer query. So, if t2.col_a does not exist, then the query turns into:

SELECT t1.col_a
FROM test_1 t1
WHERE t1.col_a IN (SELECT t1.col_a FROM test_2 t2);

The solution is to qualify all column references so there is no ambiguity.

Join on TOP 1 from subquery while referencing outer tables

If that is the logic you want, you can use OUTER APPLY:

SELECT C.ContactSys, GL.ABC, GL.XYZ,
        ... a bunch of other columns ... 
FROM Users U JOIN
     Contacts C
     ON U.ContactSys = C.ContactSys LEFT JOIN
     UserWatchList UW 
     ON U.UserSys = UW.UserSys LEFT JOIN
     Accounts A
     ON C.AccountSys = A.AccountSys OUTER APPLY
     (SELECT TOP 1 gl.*
      FROM GuestLog gl
      WHERE gl.ContactSys = C.ContactSys
      ORDER BY gl.GuestLogSys DESC
     ) GL
WHERE C.OrganizationSys = 1012 AND
      U.UserTypeSys = 2 AND
      C.FirstName = 'steve'

Referencing outer query in subquery

You can do something like this with a join:

select * from table a
    inner join (
       select id,
              max(
                  if(`date` <= __LOWERLIMIT__ ,`date`, 0)
              ) as min_date,              
              min(
                 if(`date` >= __UPPERLIMIT__ , `date`, UNIX_TIMESTAMP())
              ) as max_date
           from table
           where id = __ID__
           group by id
    ) range on
    range.id = a.id and
    a.`date` between min_date and max_date;

I'm not a MySQL expert, so apologies if a bit of syntax tweaking is needed.

Update: the OP also found this very nice solution.

PostgreSQL Referencing Outer Query in Subquery

Instead of joining an inline view based on the prices table, you can perform a subquery in the SELECT list:

SELECT customer_id, quantity, (
    SELECT price
    FROM prices p
    WHERE
      p.customer_id = o.customer_id
        AND p.effective_time <= o.timestamp
    ORDER BY p.effective_time DESC
    LIMIT 1
  ) AS price
FROM orders o

That does rely on a correlated subquery, which could be bad for performance, but with the way your data are structured I doubt there's a substantially better alternative.

mysql sub query reference outer table

This is your query, with table aliases making it a bit more readable:

SELECT SUM(trans_current_quantity) as quantity
FROM phppos_items i LEFT JOIN
     phppos_location_items li
     ON li.item_id = i.item_id and li.location_id IN (1) LEFT JOIN 
     (SELECT * 
      FROM phppos_inventory inv
      WHERE inv.trans_date < '2018-05-06 23:59:59' and
            trans_items = *XXX*
      ORDER BY trans_date DESC LIMIT 1
     ) inv 
    ON li.item_id = inv.trans_items;

I can only interpret trans_current_quantity as coming from phppos_inventory. The LEFT JOINs appear to be superfluous as you've written the query.

What you really want is a lateral join. That doesn't work in MySQL, alas. Here is the next closest thing:

SELECT SUM(inv.trans_current_quantity) as quantity
FROM phppos_items i JOIN
     phppos_location_items li
     ON li.item_id = i.item_id and li.location_id IN (1) JOIN 
     phppos_inventory inv
     ON li.item_id = inv.trans_items
WHERE li.item_id = XXX AND
      inv.trans_date = (SELECT MAX(inv2.trans_date)
                        FROM phppos_inventory inv2
                        WHERE inv2.trans_date < '2018-05-07' and
                              inv2.trans_items = li.item_id
                       );

I also fixed the date comparison by adding a second. If that's not right, you can fix it, but the query is more readable.

Best way to reference an outer query / subquery?

So, as one commenter pointed out, I think you would do much better off using JOINS, than sub-selects. For example, if I am reading your query/problem correctly, you could do a join query like this:

SELECT t1.name, t1.email, t3.job_id
FROM table_users t1
LEFT JOIN table_users_job t2
ON t1.user_id = t2.user_id
LEFT JOIN table_jobs t3
ON t3.job_id = t2.job_id
WHERE t2.period = 'night
AND t3.status = 'available_position'

Which is a lot more concise, easier to read, and is easier on your database. But doing this would prevent you from modularizing your SQL. If that is really important, you might consider storing such queries in Stored Procedure. This way, you can actually get a SP to return a list of results. Take a look at this tutorial:

http://www.wellho.net/resources/ex.php4?item=s163/stp4

Of course, that doesn't really solve your problem of being able to access variables at the lower levels of a sub select, but it would make your SQL easier to manage, and make it available to other language implementations, as you mentioned might be a need for you.

Something else to consider, in the bigger picture, would be migrating to a PHP framework that provides an ORM layer, where you could make those tables into objects, and then be able to access your data with much greater ease and flexibility (usually). But that is very 'big picture' and might not be suitable for your project requirements. One such framework that I could recommend, however, is CakePHP.

SQL query to mysql (Subquery does not recognize outer table)

The reason for your error is that you cannot access outer columns from inside a derived table (your from (...) as a). To do it, you have to write something like (...) as a where a.tid = dancer.tid, so you put the where outside of the derived table; but you obviously have to rewrite a in a way to have tid as a column.

In your case, it would be more complicated to fix your code, so I wrote you an (easier) new one:

select tid,
  CONCAT_WS("",
  (SELECT GROUP_CONCAT(d2d2.taid,"---") 
   from dance2dancer d2d2
   where d2d2.tid = d2d.tid 
   group by d2d2.tid limit 10),
  REPEAT(",0---",10-count(distinct d2d.taid))
  ) as `AllDancerWhoDance`
from dance2dancer d2d
group by d2d.tid

And a warning: this kind of query (using group_concat or concat in a column) is only to be used in a final step to format the data you want to display. If you plan to use it in another query, e.g. something like select * from dancer where INSTR('213---,345---,111---', taid) > 0, please don't, just rewrite it.

Update without using limit 10:

Although limit 10 should work with group_concat (it works for me), in case it doesn't work for you, you can of course just concat everything and then take the first 10 entries of the concated string afterwards. It would actually simplify the query, since you don't need the subquery anymore (that only was there to have the limit in the first place), but might generate a large temporary string (before substring_index) if you have a dance with A LOT of dancers.

select tid,
  SUBSTRING_INDEX(CONCAT_WS("",
  GROUP_CONCAT(d2d.taid,"---"),
  REPEAT(",0---",10-count(distinct d2d.taid)),
  ','
  ), ',',10) as `AllDancerWhoDance`
from dance2dancer d2d
group by d2d.tid;

or calculate a rownumber beforehand and just take the first 10 rows per tid:

select d2d.tid, CONCAT_WS("",
  GROUP_CONCAT(d2d.taid,"---"),
  REPEAT(",0---",10-d2d.cnt)
  ) as `AllDancerWhoDance`
from 
(select tid, taid, 
      (select count(*) 
       from dance2dancer d2d4
       where d2d4.tid = d2d3.tid
       and d2d4.taid <= d2d3.taid
      ) as rownum,
      (select count(*) 
       from dance2dancer d2d4
       where d2d4.tid = d2d3.tid
      ) as cnt
      from dance2dancer d2d3
) as d2d
where d2d.rownum <= 10
group by d2d.tid;

MySQL reference outer table alias in subquery error

I think you are trying to fetch latest 7 comments for each post. Could you try this? you can test here http://www.sqlfiddle.com/#!2/a222e/3/0

The First Attempt

I tried below SQL.

SELECT *
FROM comments t1
WHERE post_id IN (247,254,244,243,242,241)
AND id IN (
    SELECT id
    FROM comments
    WHERE t1.id = id
    LIMIT 7
);

But I got an error "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery"

Another Approach

So, I tried self join on comments to generate sequence number.

SELECT id
FROM (
    SELECT t1.id, COUNT(*) AS cnt
    FROM comments t1 INNER JOIN comments t2
      ON t1.post_id = t2.post_id
    WHERE t1.id <= t2.id
      AND t1.post_id IN (247,254,244,243,242,241)
      AND t2.post_id IN (247,254,244,243,242,241)
    GROUP BY t1.id
) x
WHERE cnt <= 7;

inner sub-query uses self join and produces cnt column which has sequential value for each comment id of post.

But preceding query only fetches id of comment

Finally to get all columns of comment table, following query should be executed.

SELECT *
FROM comments c INNER JOIN (
    SELECT id
    FROM (
        SELECT t1.id, COUNT(*) AS cnt
        FROM comments t1 INNER JOIN comments t2
          ON t1.post_id = t2.post_id
        WHERE t1.id <= t2.id
          AND t1.post_id IN (247,254,244,243,242,241)
          AND t2.post_id IN (247,254,244,243,242,241)
        GROUP BY t1.id
    ) x
    WHERE cnt <= 7
) t USING (id);

Using User Variables

Actually you have another chance using MySQL user variable. I didn't mention this interesting MySQL feature because I was not sure I understood your question correctly.

SELECT * 
FROM (
    SELECT post_id, id,
        IF (@pid = post_id, @cnt := @cnt + 1, @cnt := 1) AS cnt,
        @pid := post_id
    FROM comments, (SELECT @pid := 0, @cnt := 0) tmp
    WHERE post_id IN (247,254,244,243,242,241)
    ORDER BY post_id, id DESC
) x
WHERE cnt <= 7;

Preceding SQL looks like simpler (means good performance) than older join version. but not tested on large data set.

---

Related Topics