CODEWITHSUNDEEP
postgresql
sergeda
sergeda

Reputation: 2201

Duplicated enries in Select result on join in Postgresql

I have number of tables with sample data:

CREATE TABLE public.languages (
  "id" bigserial NOT NULL,
  PRIMARY KEY ("id")
);

CREATE TABLE public.goods (
    "id" bigserial NOT NULL,
    PRIMARY KEY ("id")
);

CREATE TABLE public.good_texts (
  goodid int8 NOT NULL,
  title varchar(250) NOT NULL,
  languageid int8 NOT NULL,
  PRIMARY KEY (goodid, languageid)
);

CREATE TABLE "public".filtervariants (
  id                        bigserial NOT NULL,
  PRIMARY KEY (id));

CREATE TABLE "public".filtervariant_good (
  filtervariantid int8 NOT NULL,
  goodid          int8 NOT NULL,
  PRIMARY KEY (goodid,
               filtervariantid));

INSERT INTO "public"."languages" VALUES ('1');

INSERT INTO "public"."goods" VALUES ('385');

INSERT INTO "public"."good_texts" VALUES ('385', 'Dell Inspiron 5559', '1');

INSERT INTO "public"."filtervariants" VALUES ('1');
INSERT INTO "public"."filtervariants" VALUES ('2');
INSERT INTO "public"."filtervariants" VALUES ('3');
INSERT INTO "public"."filtervariants" VALUES ('4');
INSERT INTO "public"."filtervariant_good" VALUES ('1', '385');
INSERT INTO "public"."filtervariant_good" VALUES ('3', '385');

This query

SELECT
    gt.*, goods.*
FROM
    good_texts gt
JOIN goods ON goods. ID = gt.goodid
JOIN filtervariant_good ON goods.id = filtervariant_good.goodid
WHERE
    gt.languageid = 1

returns record two times and I can't understand why. Whenever I remove 

JOIN filtervariant_good ON goods.id = filtervariant_good.goodid

line it returns record only once

Please explain what's wrong with this join. If I join this table with other tables (not shown here) it works just fine.

Upvotes: 2

Views: 24

Answers (1)

redneb
redneb

Reputation: 23850

Well, there are two rows in filtervariant_good with goodid equal to 385, that's why. I suspect that you don't care about having multiple rows in that table, and you are only interested whether a good exists in that table. If my guess is correct, then you can do something like the following instead:

SELECT
    gt.*, goods.*
FROM
    good_texts gt
JOIN goods ON goods. ID = gt.goodid
WHERE
    gt.languageid = 1
    AND exists(SELECT FROM filtervariant_good WHERE goods.id = filtervariant_good.goodid)

This will return only the goods that exist in filtervariant_good.

Upvotes: 1

Related Questions