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
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
(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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With