CODEWITHSUNDEEP
mysql
Zaka
Zaka

Reputation: 11

How to concatenate two columns of different mySQL select statements?

How can I combine the following two mySQL select statements so that I get each one in different columns but same table?

select a.property as p1 from T1 a inner join T2 b on a.id = b.id1;

select a.property as p2 from T1 a inner join T2 b on a.id = b.id2;

Each of these statements work separately and will return the same number of rows. I just want to get both results in two columns, in the same table.

Upvotes: 1

Views: 988

Answers (2)

Zane Bien
Zane Bien

Reputation: 23125

You can use this solution:

SELECT
    a.property AS p1, b.property AS p2
FROM
(
    SELECT a.property, @rn1:=@rn1+1 AS rn
    FROM T1 a
    INNER JOIN T2 b ON a.id = b.id1
    CROSS JOIN (SELECT @rn1:=0) var_init
) a
INNER JOIN
(
    SELECT a.property, @rn2:=@rn2+1 AS rn
    FROM T1 a
    INNER JOIN T2 b ON a.id = b.id2
    CROSS JOIN (SELECT @rn2:=0) var_init
) b ON a.rn = b.rn

Edit: Here is a variation of Madhivanan's solution which should be simpler:

SELECT   MAX(CASE WHEN a.id=b.id1 THEN a.property END) AS p1,
         MAX(CASE WHEN a.id=b.id2 THEN a.property END) AS p2
FROM     T1 a 
JOIN     T2 b ON a.id IN (b.id1,b.id2)
GROUP BY a.id

We're adding GROUP BY and MAX so that we don't get two rows per id, and we just select the maximum of the case expression which gets the non-null values for each group (essentially puts the properties in each column on the same row for each id).

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

Use it in a single statement like this

select 
     case when a.id=b.id1 then a.property end as p1,
     case when a.id=b.id2 then a.property end as p2
from 
     T1 a inner join T2 b on a.id in (b.id1,b.id2)

Upvotes: 2

Related Questions