CODEWITHSUNDEEP
sqlpostgresqljoinunion
Jeiman
Jeiman

Reputation: 1181

Convert a row with two columns into two rows of one column

I have two columns, confid1 and confid2. They both have the same data type.

I somehow need to bring confid2 into confid1, but not concatenate them. Instead, insert it into a new line under confid1.

Original:

confid1     confid2
AACII1C1    AACII1C2

I have tried using UNION such as below:

(SELECT confid1 as ID1 FROM dyndomrun)
UNION
(SELECT confid2 as ID2 FROM dyndomrun)

It then returns the combination of confid1 and confid2 inside confid1, which is what I was looking for, as below:

confid1
AACII1C1
AACII1C2

Now, the problem is that one of the columns in another table links up with confid1 and confid2, but it is all in a single column but multiple rows.

Right now, I have tried using the same UNION method, just adding the pdbcode into the code, such as below:

(SELECT confid1 as id1, conformer.pdbcode from dyndomrun, conformer where dyndomrun.confid1 = conformer.id)
UNION
(SELECT confid2 as id2, conformer.pdbcode from dyndomrun, conformer where dyndomrun.confid1 = conformer.id)

And it returns duplicate "pdbcode" values when it's not suppose to, as below:

confid1,    pdbcode
AACII1C1    2a4n
AACII1C2    2a4n

I want it to select and return a pdbcode column related to confid1 and confid2 such as below:

confid1,    pdbcode
AACII1C1    2a4n
AACII1C2    1b87

Upvotes: 4

Views: 12789

Answers (4)

Lamak
Lamak

Reputation: 70668

SELECT A.confid1, B.pdbcode
FROM (  SELECT confid1 FROM dyndomrun
        UNION
        SELECT confid2 FROM dyndomrun) A
LEFT JOIN conformer B
ON A.confid1 = B.id

Upvotes: 1

gbn
gbn

Reputation: 432541

Change your 2nd JOIN to match using confid2?

(SELECT confid1 as id1, conformer.pdbcode 
from dyndomrun JOIN conformer 
           ON dyndomrun.confid1 = conformer.id)
UNION
(SELECT confid2 as id2, conformer.pdbcode 
 from dyndomrun JOIN conformer 
           ON dyndomrun.confid2 = conformer.id)

Note: updated to use explicit JOIN syntax

Upvotes: 1

Andomar
Andomar

Reputation: 238246

You could make the join condition span both:

select  *
from    dyndomrun ddr
join    conformer as c
on      c.id in (ddr.confid1, ddr.confid2)

Alternatively, you can move the union to a subquery, and join that to the second table:

select  *
from    (
        select  confid1 as confid
        from    dyndomrun
        union all
        select  confid2 
        from    dyndomrun
        ) as ddr
join    conformer as c
on      c.id = ddr.confid

Upvotes: 1

Eugen Rieck
Eugen Rieck

Reputation: 65314

(SELECT confid1 as id1, conformer.pdbcode from dyndomrun, conformer where dyndomrun.confid1 = conformer.id)
UNION
(SELECT confid2 as id2, conformer.pdbcode from dyndomrun, conformer where dyndomrun.confid2 = conformer.id)

Upvotes: 1

Related Questions