CODEWITHSUNDEEP
sqlpostgresqljoinview
Jeiman
Jeiman

Reputation: 1181

Possible to combine two tables without losing all data by using JOINS

I have a table as below and I would like to know if I can still join them together, without losing existing data from both tables when they are combined by referencing JOIN methods.

Table details - VIEW Table

SELECT
  r.domainid,
  r.DomainStart,
  r.Domain_End,
  r.ddid,
  r.confid,
  r.pdbcode,
  r.chainid,
  d.pdbcode AS "CATH_PDBCODE",
  d.cathbegin AS "CATH_BEGIN",
  d.cathend AS "CATH_END"
FROM dyndom_domain_table r
  JOIN cath_domains d ON d.pdbcode::character(4) = r.pdbcode 
  ORDER BY confid ASC;

As you can see, dyndom_domain_table is a VIEW Table that I have created to make it easier for me to use JOIN clauses with the other table that has the same pdbcode.

So far it just returns all of the data that matches with the PDB Code. What I would like to do is return all of the data that both matches and doesn't match each other's PDB Code.

Is there a rule in which I can apply it to? Or is it not possible?

Upvotes: 2

Views: 3991

Answers (2)

Eve Freeman
Eve Freeman

Reputation: 33145

I think you're asking for a left join, but I'm not sure.

SELECT
  r.domainid,
  r.DomainStart,
  r.Domain_End,
  r.ddid,
  r.confid,
  r.pdbcode,
  r.chainid,
  d.pdbcode AS "CATH_PDBCODE",
  d.cathbegin AS "CATH_BEGIN",
  d.cathend AS "CATH_END"
FROM dyndom_domain_table r
  LEFT JOIN cath_domains d ON d.pdbcode::character(4) = r.pdbcode 
  ORDER BY confid ASC;

Upvotes: 1

Ernest Friedman-Hill
Ernest Friedman-Hill

Reputation: 81684

I believe you want a FULL OUTER JOIN rather than just a JOIN (which is, by default, an INNER JOIN). In a FULL OUTER JOIN, every row in each table will correspond to some row in the result table; rows from one table that don't match the other will be extended with NULLs to fill the missing column.

So FULL OUTER JOIN instead of just JOIN, and that should do you.

Upvotes: 5

Related Questions