CODEWITHSUNDEEP
mysqlsql
John
John

Reputation: 21927

MySQL - Use select field value in nested SQL statement of the same SQL

I have the following SQL query

SELECT 
    a.id AS ID, a.title, 
    (
        SELECT Group_Concat( title ) 
        FROM (
            SELECT title
            FROM `table_b` AS b
            JOIN table_c ON c.id = b.id
            WHERE b.id = ID
            UNION 
            SELECT title
            FROM `table_b` AS b
            JOIN table_c ON c.id = b.id
            WHERE b.another_id = ID
    ) AS other_titles 
FROM table_a

However it is not working and is having a problem with the parts WHERE b.id = ID and WHERE b.another_id = ID in the nested SQL part.

How can I use the ID from the first select (SELECT a.id AS ID) in the nested select?

Upvotes: 4

Views: 7788

Answers (2)

Andriy M
Andriy M

Reputation: 77687

I think what you have here is called correlated subquery. It looks quite promising and seems to only lack a couple of final strokes (highlighted in bold italic):

SELECT
  a.id AS ID,
  a.title, 
  (
    SELECT Group_Concat( title ) 
    FROM (
      SELECT title
      FROM `table_b` AS b
        JOIN table_c ON c.id = b.id
      WHERE b.id = a.ID
      UNION 
      SELECT title
      FROM `table_b` AS b
        JOIN table_c ON c.id = b.id
      WHERE b.another_id = a.ID
  ) AS other_titles 
FROM table_a AS a

Keep in mind that what you are actually referencing in the subquery is a.id, not the ID alias. (You can't reference the alias there.)

If a.id is a complex expression and you are not very happy about repeating it in the subquery, then you might need to restructure the entire query, maybe like this:

SELECT
  x.ID,
  x.title,
  (
    SELECT Group_Concat( title ) 
    FROM (
      SELECT title
      FROM `table_b` AS b
        JOIN table_c ON c.id = b.id
      WHERE b.id = x.ID
      UNION 
      SELECT title
      FROM `table_b` AS b
        JOIN table_c ON c.id = b.id
      WHERE b.another_id = x.ID
  ) AS other_titles 
FROM (
  SELECT
    a.id AS ID,
    a.title,
  FROM table_a AS a
) x

Upvotes: 4

Atticus
Atticus

Reputation: 6720

You cannot select values into the FROM clause. You may only use variables to dynamically set the table name.

Upvotes: 0

Related Questions