CODEWITHSUNDEEP
mysqlselect
CodeSlayer
CodeSlayer

Reputation: 1327

Mysql add variable in select statement

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

Upvotes: 0

Views: 71

Answers (2)

Cristian Porta
Cristian Porta

Reputation: 4433

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;

Upvotes: 1

Sebas
Sebas

Reputation: 21542

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.

Upvotes: 3

Related Questions