0

I am dealing with two tables: "dyndomrun.ddid" = with a Primary key and "domainregion.domainid" = without any primary key nor foreign key.

"dyndomrun.ddid" definition is "character varying" and has 8 characters, whereas "domainregion.domainid" definition is also "character varying" but has 10 characters.

Problem : There are some fields in domainregion table that needs to be joined together with the primary key in dyndomrun table. I can't seem to do this using a simple JOIN statement such as below:

SELECT domainregion.domainid, domainregion.dombegin, domainregion.domend, dyndomrun.ddid 
FROM domainregion, dyndomrun 
WHERE domainregion.domainid = dyndomrun.ddid 
ORDER BY domainregion.domainid, dyndomrun.ddid, domainregion.dombegin, domainregion.domend;

I have tried JOINS, INNER JOINS, LIKE, none of them seems to work. The database that I am dealing with is purely SQL based stored using PostgreSQL.

Is there a method that i can use in which it selects the domainregion.domainid based on the length of the character and matching it dyndomrun.ddid?

Please advise.

Jeiman
  • 1,121
  • 9
  • 27
  • 50
  • cast the larger of the 2 to the other datatype/precision when doing the join – xQbert Dec 26 '11 at 15:27
  • 1
    where left(domaainregion.domainID,8)=left(dyndomrun.ddid,8) may need padding of spaces. (but this is the general idea, not a "SPECIFIC answer" – xQbert Dec 26 '11 at 15:35
  • Is is something like this `select CAST (domainregion.domainid AS "DomainRegion" AND dyndomrun.ddid AS "DynDomrun") from domainregion, dyndomrun where left(domainregion.domainid,8)=left(dyndomrun.ddid,8) order by domainregion.domainid, dyndomrun.ddid;` – Jeiman Dec 26 '11 at 15:44

1 Answers1

1

Try this to compare the first 8 characters only:

SELECT r.domainid, r.dombegin, r.domend, d.ddid 
FROM   domainregion r
JOIN   dyndomrun d ON r.domainid::varchar(8) = d.ddid 
ORDER  BY r.domainid, d.ddid, r.dombegin, r.domend;

The cast implicitly trims trailing characters. ddid only has 8 characters to begin with. No need to process it, too. This achieves the same:

JOIN   dyndomrun d ON left(r.domainid, 8) = d.ddid

However, be advised that the string function left() was only introduced with PostgreSQL 9.1. In earlier versions you can substitute:

JOIN   dyndomrun d ON substr(r.domainid, 1, 8) = d.ddid

__

Basic explanation for beginners:

  • The query uses a JOIN. Read more about that in the manual.

  • FROM domainregion r is short for FROM domainregion AS r. AS is just noise in this case in PostgreSQL. The table alias makes the query shorter and easier to read but has no other impact in here. You can also use table aliases to include the same table multiple times for instance.

  • The join condition ON r.domainid::varchar(8) = d.ddid joins only those rows together where the two expressions match exactly. Again, read about those basics in the manual (or any other source).

It's a simple query, not much to explain here.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • may i ask what does domainregion "r" stands for ? – Jeiman Dec 27 '11 at 13:40
  • 1
    it's an alias so you don't have to type domainregion every time. where you see r.domainid it means substitute domainregion for "R" – xQbert Dec 27 '11 at 13:41
  • Thank you for the clear explanation. However, i have another problem. Do you mind taking a look at it for me . [link](http://stackoverflow.com/questions/8645230/join-three-tables-with-one-of-them-not-having-a-primary-key) – Jeiman Dec 27 '11 at 13:47
  • @jeiman90: I added some basic explanation and links to my answer. – Erwin Brandstetter Dec 27 '11 at 14:12