Select multiple rows without duplicate records

1

Hello, I'm trying to select one record from tbl_sheet and multiple blob images from tbl_notation. I'm using SQLite/Pascal. Here's my database structure:

CREATE TABLE "tbl_notation" (
  "int_repno"	INTEGER,
  "int_pageno"	INTEGER,
  "id_reptype"  INTEGER,
  "blob_sheet"	MEDIUMBLOB,
  FOREIGN KEY("id_reptype") REFERENCES "tbl_reptype"("id")
);
CREATE TABLE "tbl_person" (
  "id"	INTEGER NOT NULL,
  "txt_person"	VARCHAR(255) UNIQUE,
  PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE "tbl_place" (
  "id"	INTEGER NOT NULL,
  "txt_place"	VARCHAR(255) NOT NULL UNIQUE,
  PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE TABLE "tbl_reptype" (
  "id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE ,
  "txt_reptype" VARCHAR(255)
);
CREATE TABLE "tbl_sheet" (
  "id"	INTEGER NOT NULL,
  "int_rep_no"	INTEGER,
  "txt_song_name"	VARCHAR(255),
  "id_reptype"	INTEGER,
  "id_place"	INTEGER,
  "id_source_by"	INTEGER,
  "id_compiled_by"	INTEGER,
  "id_notation_by"	INTEGER,
  "id_type"	INTEGER,
  "txt_lyrics" VARCHAR(1200),
  PRIMARY KEY("id" AUTOINCREMENT),
  FOREIGN KEY("id_type") REFERENCES "tbl_type"("id"),
  FOREIGN KEY("id_compiled_by") REFERENCES "tbl_person"("id"),
  FOREIGN KEY("id_source_by") REFERENCES "tbl_person"("id"),
  FOREIGN KEY("id_place") REFERENCES "tbl_place"("id"),
  FOREIGN KEY("id_notation_by") REFERENCES "tbl_person"("id"),
  FOREIGN KEY("id_reptype") REFERENCES "tbl_reptype"("id")
);
CREATE TABLE "tbl_type" (
  "id"	INTEGER NOT NULL,
  "txt_type"	TEXT UNIQUE,
  PRIMARY KEY("id" AUTOINCREMENT)
);

Here's my sql query

SELECT DISTINCT
	*,
	rt.id,
	p.id,
	so.id,
	c.id,
	n.id,
	t.id,
	*,
	no.int_repno
FROM tbl_sheet s
LEFT JOIN tbl_reptype rt
ON rt.id = s.id_reptype
LEFT JOIN tbl_place p
ON p.id = s.id_place
LEFT JOIN tbl_person so
ON so.id = s.id_source_by
LEFT JOIN tbl_person c
ON c.id = s.id_compiled_by
LEFT JOIN tbl_person n
ON n.id = s.id_notation_by
LEFT JOIN tbl_type t
ON t.id = s.id_type
LEFT JOIN tbl_notation no
ON no.int_repno = s.int_rep_no;

My code selects about 60 columns and duplicate records.

How can I select one record from tbl_sheet and multiple rows from tbl_notation?

2

Most likely the problem is that one or more of these joins matches multiple rows in a way you aren't expecting.

Create separate queries joining tbl_sheet to only one other table at a time (in total, 7 queries) and see how many rows come back per row in tbl_sheets.

Like this:

SELECT s.*, rt.*
FROM tbl_sheet s 
LEFT JOIN tbl_reptype rt 
ON rt.id = s.id_reptype
ORDER BY s.id;

Use that pattern for all of the joined tables.

Look through the results for examples where s.id repeats. That is where your duplication is coming from.

1 Like
4

Hmm, your code should already be selecting one row from sheet and multiple rows from notation, if they exist. Unless somehow you have duplicate ids in one or more of the other tables. Be sure to also specify a UNIQUE constraint in all those tables as well.

Based on a quick Google for doc for SQLLite, if "AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY" that should prevent reuse of values, if you don't specify a specific id value when you insert the row. Make sure you are not specifying a duplicate id value when you insert rows (and verify that the tables do not have dup ids after loading them).

1 Like