CODEWITHSUNDEEP
sqlpostgresqljoinforeign-keysforeign-key-relationship
rfg
rfg

Reputation: 1636

Implicitly refer to foreign key table field in sql query

I want to know how to reference foreign key table fields in select query (in PostgreSQL 9.3). I have following tables:

CREATE TABLE "Threads" (
  "Id" integer NOT NULL,
  "Board" char(30) NOT NULL,
CONSTRAINT "Threads_pkey"
   PRIMARY KEY ("Id", "Board"));

CREATE TABLE "Posts" (
  "PostId" integer NOT NULL,
  "Text" text,
  "Thread" integer NOT NULL,
CONSTRAINT "Posts_pkey"
  PRIMARY KEY ("PostId"),
CONSTRAINT "Thread"
  FOREIGN KEY ("Thread") REFERENCES "Threads"("Id"));

I want to output the following fields: PostId, Text, Board. Earlier (not in PostgreSQL) I wrote it like this:

SELECT 
  "PostId", 
  "Text", 
  "Thread"->"Board"
FROM "Posts";

PostgreSQL seems to require explicit join:

SELECT 
  "PostId",
  "Text",
  "Board"
FROM "Posts"
INNER JOIN "Threads" ON ("Posts"."Thread" = "Threads"."Id")

Is there any way to write this query without stating this obvious join? I also tried this variant:

SELECT 
  "PostId", 
  "Text", 
  "Thread"."Board"
FROM "Posts";

but that has thrown an error too.

Upvotes: 0

Views: 172

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125444

If you create a view:

create view post_thread as
select *
from 
    "Posts" p
    inner join
    "Threads" t on t.id = p.thread

It will be possible to query without a join:

select *
from post_thread

BTW it is a bad idea to create double quoted identifiers.

Upvotes: 1

Related Questions