0

I have three tables that i would like to join up together.

Domain table
-domainid - Primary key

DomainRegion table
-domainid - Don't have a Primary Key or Foreign Key
-dombegin
-domend

DynDomrun table
-ddid - Primary Key
-confid1
-confid2

"domain.domainid", "domainregion.domainid" and "dyndomrun.ddid" have the same type of data types, "character varying".

Now, the problem is that "domain.domainid" and "domainregion.domainid" has two extra characters in it that i can't seem to do a join with "dyndomrun.ddid".

However, "domainid" is related to "ddid", just that "domainregion.domainid" is not assigned a primary key or a foreign key and i need some fields from that table to join up with the others.

In the end, I need to have a single, full table listing the "dyndomrun.ddid, domainregion.dombegin, domainregion.domend, dyndomrun.confid1, dyndomrun.confid2".

I have tried using where ("domain.domainid",8)=left("dyndomrun.ddid",8), it gave me an error stating

ERROR: function left(character varying, integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts.

I have tried using joins, and other sql statements and they all don't seem to work.

Any ideas as to how to solve this problem?

Thank you.

Jeiman
  • 1,121
  • 9
  • 27
  • 50
  • As Erwin said in the original topic, the left() function only exists for postgres >= 9.x. Use substr() instead. – wildplasser Dec 27 '11 at 13:39
  • possible duplicate of [How to join two tables with one of them not having a primary key and not the same character length](http://stackoverflow.com/questions/8636956/how-to-join-two-tables-with-one-of-them-not-having-a-primary-key-and-not-the-sam) – Erwin Brandstetter Dec 27 '11 at 13:50
  • Ok. Thank you very much. Your previous solution did work. It now returns all the required rows that i need to make a comparison. – Jeiman Dec 27 '11 at 13:55
  • Do you mind explaining as to how your solution works? Thank you. – Jeiman Dec 27 '11 at 13:56

1 Answers1

1

In addition to what I answered here, you also need to read up on the syntax of identifiers in PostgreSQL.

This:

"domain.domainid"

would have to be 

"domain"."domainid"

Or better yet, simply:

domain.domainid
Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thank you Erwin. As to your solution you posted in my post earlier, do you mind explaining it to me as I am a beginner at SQL. Thank you again. – Jeiman Dec 27 '11 at 14:00