CODEWITHSUNDEEP
sqlpostgresqljoininner-joinunion-all
Jeiman
Jeiman

Reputation: 1181

Combine pre-joined tables with another table

I am trying to join a pre-joined table with another table side by side, but it doesnt seem to work.

Here's the code :

SELECT
  r.domainid,
  r.dombegin AS DomainStart,
  r.domend AS Domain_End,
  d.ddid,
  d.confid1 AS confid,
  c.pdbcode,
  c.chainid,
  a.pdbcode AS "cath_pdbcode", 
  c.pdbcode
FROM dyn_dyndomrun d, cath_domains a
  INNER JOIN dyn_conformer c ON d.confid1 = c.id
  INNER JOIN dyn_domainregion r ON r.domainid::varchar(8) = d.ddid
  INNER JOIN dyn_conformer AS c ON a.pdbcode::character(4) = c.pdbcode
UNION ALL
SELECT
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  d.ddid,
  d.confid2,
  c.pdbcode,
  c.chainid
FROM dyn_dyndomrun d 
  INNER JOIN dyn_conformer c ON d.confid2 = c.id
  ORDER BY confid ASC

There is something wrong at this line

FROM dyn_dyndomrun d, cath_domains a
      INNER JOIN dyn_conformer c ON d.confid1 = c.id
      INNER JOIN dyn_domainregion r ON r.domainid::varchar(8) = d.ddid
      INNER JOIN dyn_conformer AS c ON a.pdbcode::character(4) = c.pdbcode

Here's the error :

ERROR:  invalid reference to FROM-clause entry for table "d"
LINE 11:   INNER JOIN dyn_conformer c ON d.confid1 = c.id
                                         ^
HINT:  There is an entry for table "d", but it cannot be referenced from this part of the query.


********** Error **********

ERROR: invalid reference to FROM-clause entry for table "d"
SQL state: 42P01
Hint: There is an entry for table "d", but it cannot be referenced from this part of the query.
Character: 236

At the end, I would like to have a table in which it has, "domainid, domainstart, domainend, ddid, confid, chainid, pdbcode from conformer and the chain id" and along side that, i would like to have a new set of columns from another table, such as "pdbcode from cath_domains, cathbegin, cathend".

The pdbcode from conformer and cath_domains match each other and therefore i would like to cross-reference them.

Am I doing it the wrong way?

Upvotes: 0

Views: 609

Answers (1)

Chris Travers
Chris Travers

Reputation: 26454

Stop doing implicit cross joins. Also as ypercube pointed out, a few other little things need to be cleaned up, particularly removing the duplicate join.

SELECT
  r.domainid,
  r.dombegin AS DomainStart,
  r.domend AS Domain_End,
  d.ddid,
  d.confid1 AS confid,
  c.pdbcode,
  c.chainid,
  a.pdbcode AS "cath_pdbcode", 
  c.pdbcode
FROM dyn_dyndomrun d
  INNER JOIN dyn_conformer c ON d.confid1 = c.id 
  INNER JOIN dyn_domainregion r ON r.domainid::varchar(8) = d.ddid
  INNER JOIN cath_domains a ON a.pdbcode::character(4) = c.pdbcode
UNION ALL
SELECT
  NULL,
  NULL,
  NULL,
  NULL,
  NULL,
  d.ddid,
  d.confid2,
  c.pdbcode,
  c.chainid
FROM dyn_dyndomrun d 
  INNER JOIN dyn_conformer c ON d.confid2 = c.id
  ORDER BY confid ASC

Upvotes: 2

Related Questions