Mysql add variable in select statement

Question

I have a query like this

SELECT a.id AS col1, b.id AS col2, b.title AS col3
FROM tbl1 a
INNER JOIN tbl2 b ON a.tbl2_id=b.id

this query is working properly, now if i make a subquery and pass the value of col2, like this

SELECT a.id AS col1, b.id AS col2, b.title AS col3, (
    SELECT a.name
    FROM tbl1 a
    INNER JOIN tbl2 b ON a.tbl2_id=b.id
    WHERE a.id= [value of col2]
)
FROM tbl1 a INNER JOIN tbl2 b ON a.tbl2_id=b.id

how can i achived to pass the value of col2 in subquery? Thanks in advance

use this subquery in another join condition. You can copy paste it exactly like this with the parentheses, giving it an alias, in a join condition. You will have to adapt your recordsets. — Sebas
– Sebas, Commented Oct 23, 2014 at 4:06

Sebas · Accepted Answer · 2014-10-23 04:12:26Z

3

If I just transform your query to do what you want, here is the result:

SELECT 
    a.id AS col1, 
    b.id AS col2, 
    b.title AS col3,
    t.name
FROM 
    tbl1 a 
        JOIN tbl2 b ON a.tbl2_id = b.id
        JOIN (
            SELECT a.id, a.name 
            FROM tbl1 a 
                JOIN tbl2 b ON a.tbl2_id = b.id 
            ) t ON t.id = b.id;

But I'm pretty sure you can simplify it. It is not clear to me as to why you want to join it that way, but probably it is because the table names are not the real ones.

answered Oct 23, 2014 at 4:12

Sebas

21.6k9 gold badges60 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cristian Porta · Accepted Answer · 2014-10-23 06:13:01Z

1

Also you can use the MySQL User-Defined Variables in order to avoid multiple joins and keep the code "more readable"

SELECT a.id AS col1, @myBCol2Value := b.id AS col2, b.title AS col3, (
  SELECT a.name
  FROM tbl1 a
  INNER JOIN tbl2 b ON a.tbl2_id = b.id
  WHERE a.id = @myBCol2Value
) AS name
FROM tbl1 a
INNER JOIN tbl2 b ON a.tbl2_id = b.id;

answered Oct 23, 2014 at 6:13

Cristian Porta

4,4433 gold badges21 silver badges26 bronze badges

Collectives™ on Stack Overflow

Mysql add variable in select statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related