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
5

hi

hope this helps

changed to SQL Server data types etc

create tables script

CREATE TABLE tbl_type (
id INT PRIMARY KEY IDENTITY(1, 1),
txt_type varchar(500) unique
);

CREATE TABLE tbl_reptype (
id INT PRIMARY KEY IDENTITY(1, 1),
txt_reptype VARCHAR(255)
);

CREATE TABLE tbl_notation (
int_repno INTEGER,
int_pageno INTEGER,
id_reptype INTEGER,
blob_sheet VARBINARY(MAX),
FOREIGN KEY(id_reptype) REFERENCES tbl_reptype(id)
);
CREATE TABLE tbl_person (
id INT PRIMARY KEY IDENTITY(1, 1),
txt_person VARCHAR(255) UNIQUE
);

CREATE TABLE tbl_place (
id INT PRIMARY KEY IDENTITY(1, 1),
txt_place VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE tbl_sheet (
id INT PRIMARY KEY IDENTITY(1, 1),
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),
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)
);

insert data script

-- 1) Seed base referenced tables (if not already populated)
INSERT INTO tbl_type (txt_type) VALUES ('Type 1'), ('Type 2'), ('Type 3');
GO

INSERT INTO tbl_reptype (txt_reptype) VALUES ('RepType A'), ('RepType B'), ('RepType C');
GO

INSERT INTO tbl_person (txt_person) VALUES ('Person X'), ('Person Y'), ('Person Z');
GO

INSERT INTO tbl_place (txt_place) VALUES ('Place 1'), ('Place 2'), ('Place 3');
GO

-- 2) Seed a representative sheet row
-- Assumes id_reptype = 1 exists
INSERT INTO tbl_sheet (
int_rep_no,
txt_song_name,
id_reptype,
id_place,
id_source_by,
id_compiled_by,
id_notation_by,
id_type,
txt_lyrics
) VALUES (
100,
'Demo Song for Notation',
1, -- id_reptype
1, -- id_place
1, -- id_source_by
1, -- id_compiled_by
1, -- id_notation_by
1, -- id_type
'Lyrics sample for the demo song'
);
GO

-- 3) Seed multiple notations linked to the same sheet (blob blobs)
INSERT INTO tbl_notation (int_repno, int_pageno, id_reptype, blob_sheet) VALUES
(100, 1, 1, CAST('ImageDataPage1' AS VARBINARY(MAX))),
(100, 2, 1, CAST('ImageDataPage2' AS VARBINARY(MAX))),
(100, 3, 1, CAST('ImageDataPage3' AS VARBINARY(MAX)));
GO

image
image967×748 13.1 KB

image
image1167×525 10.9 KB

6

Hi

can you explain what you mean by
multiple rows without duplicate records

your query is fetching data like this

image
image358×139 1.61 KB

age , name columns data is the same
but id column data is different

this is a very common scenario i have faced in my career
one solution is to choose which row out of id column you want to pick

typically depends on business requirements

dont care any one row TOP 1 can be used
need only the last one TOP 1 order by desc can be used

Thanks
:winking_face_with_tongue: