Select from multiple tables in SQLite lazarus / 21-General ---------------------------------------------------------------------- nikel · 2024-10-18 12:52 Hello, I'm trying to display song properties on my form. However my sql query shows one record multiple times which is equivalent to number of sheet notation (images). It returns nearly 60 columns. Here's my database structure: Code: Text  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i with SQLQuery1.SQL dobegin  Text:='SELECT DISTINCT';  Add('*,');  Add('rt.id,');  Add('p.id,');  Add('so.id,');  Add('c.id,');  Add('n.id,');  Add('t.id,');  Add('*,');  Add('no.int_repno');  Add('FROM tbl_sheet s');  Add('LEFT JOIN tbl_reptype rt');  Add('ON rt.id = s.id_reptype');  Add('LEFT JOIN tbl_place p');  Add('ON p.id = s.id_place');  Add('LEFT JOIN tbl_person so');  Add('ON so.id = s.id_source_by');  Add('LEFT JOIN tbl_person c');  Add('ON c.id = s.id_compiled_by');  Add('LEFT JOIN tbl_person n');  Add('ON n.id = s.id_notation_by');  Add('LEFT JOIN tbl_type t');  Add('ON t.id = s.id_type');  Add('LEFT JOIN tbl_notation no');  Add('ON no.int_repno = s.int_rep_no;');  // Add('GROUP BY s.id');end; How can I display one record and multiple images? ---------------------------------------------------------------------- eny · 2024-10-18 14:12 No idea what you try to accomplish; there are no images in your SQL tables. Cause of the duplicates: multiple records in the related tables which, so far, has been hidden by the 'DISTINCT *'. ---------------------------------------------------------------------- nikel · 2024-10-18 14:30 Thanks for the reply. I have blob_sheet column in tbl_notation. ---------------------------------------------------------------------- Hansvb · 2024-10-18 18:03 Probably won't solve your problem, but your select starts wrong because If I see it correctly, you do select distinct *, field_1, field_2, *. That can't be right. You select everything which is done with the help of: Select * or you do a select distinct field1, field2 etc. ---------------------------------------------------------------------- rvk · 2024-10-18 18:36 Quote from: Hansvb on October 18, 2024, 08:03:22 pmProbably won't solve your problem, but your select starts wrong because If I see it correctly, you do select distinct *, field_1, field_2, *. That can't be right. You select everything which is done with the help of: Select * or you do a select distinct field1, field2 etc. The first should have probably been s.* and the second no.*. But yes, kinda refuntant now (I wonder if this even runs correctly on all databases). I still don't understand the question. @nikel Couldn't you just iterate over the records and show the images. How did your desired form look like? You say you have 60 columns? From what table is that? Did you mean you get 60 records in the result dataset? (With 1 column changing each time for image) Maybe you want to have the images in the TDBGrid. Something like this https://forum.lazarus.freepascal.org/index.php?topic=46853.0 ---------------------------------------------------------------------- nikel · 2024-10-19 08:56 Thanks for the replies. QuoteHow did your desired form look like?I attached a screenshot of current form. At the bottom it must show 1 or up to 25 images for each record. But it currently shows duplicate duplicate records instead. QuoteYou say you have 60 columns?Yes, I attached another screenshot. Quote@nikel Couldn't you just iterate over the records and show the images.I haven't thought about that. Thanks, good idea. ---------------------------------------------------------------------- rvk · 2024-10-19 10:04 Quote from: nikel on October 19, 2024, 10:56:01 amQuoteYou say you have 60 columns?Yes, I attached another screenshot. So do you want, there where the small box "notulan" is, more boxes with images? If you are going to load 25 images then that might be slow when scrolling through the records. And... how are 25 images going to fit there on that screen? You would need some kind horizontal scrolbox where you can scroll through the images horizontally. Or did you have something else in mind? Anyway... the current Query is incorrect because it shows duplicate records. If you want single records, you need to take out the images-fields. You only select and show the records (without images). After selecting one record, you would create a second query, querying all the images. You can create the TImage dynamically below the grid (like you did now), but you would need to clear them when selecting the next record. It would be probably best to put them in a TScrollBox. (a horizontal scrolling TDBGrid which supports images would be even better but I'm don't know such component) Could look something like the image below.