0

I have the following tables:

CREATE TABLE "itemDetails"
(
    id smallint,
    name character varying(32) NOT NULL,
    description character varying(255) NOT NULL,
    PRIMARY KEY (id)
)

CREATE TABLE items
(
    id int,
    "detailsId" smallint REFERENCES "itemDetails" (id),
    "ownerId" int,   -- REFERENCES users (id),
    "condition" int NOT NULL DEFAULT 100,
    PRIMARY KEY (id)
)

The itemDetails table stores all the static information of an item, while the items table stores each individual instance of an item, with columns such as the condition and ownerId.

If I where to get all items owned by a user by doing the following:

SELECT * FROM items WHERE "ownerId" = 5;

How would I get the name and description of those owned items using the foreign key?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Michael Smith
  • 1,271
  • 1
  • 8
  • 31

1 Answers1

4

Use a plain join:

SELECT i.*, d.name, d.description
FROM   items i
JOIN   "itemDetails" d ON d.id = i."detailsId"
WHERE  "ownerId" = 5;

Read the manual about the FROM clause here.

Aside: Avoid double-quoted CaMeL-case identifiers in Postgres. Makes your life easier. See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228