SQL Statement or Display Object in Browse View

[kev1n]

If you omit

Code: Select all

JOIN discografie ON discografie.discografie_id = disco_lpcdbox

will it work as expected?

Also try a RIGHT OUTER JOIN on discografie

[Mr71]

HELLO KEVIN, I answer you only now because I have not had a chance to try your solution before. It doesn't work the same... it does not matter, I have revised the logic of the information in my database and I have instead provided a list where only artists without albums are displayed. Of course if my problem is useful for the forum, I am always available to implement other solutions ... Thank you always for your availability .. See you soon ...

[kev1n]

It would greatly help if you could export the database schema (without data) of the used tables:

lpcdbox, artista, nazione, generi, discografie

Use phpMyAdmin, Export and select those tables and start the export.

export.jpg

[Mr71]

-- phpMyAdmin SQL Dump
-- version 5.1.2-dev+20210713.aae0630b78
-- https://www.phpmyadmin.net/
--
-- Host: xx.xx.xx.xx
-- Generation Time: Dec 19, 2021 at 09:33 AM
-- Server version: 5.7.33-36-log
-- PHP Version: 7.4.24

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `Sqlxxxxxx_3`
--

-- --------------------------------------------------------

--
-- Table structure for table `artista`
--

CREATE TABLE `artista` (
`artista_id` varchar(25) NOT NULL,
`id_artista` varchar(30) DEFAULT NULL,
`des_artista` varchar(50) DEFAULT NULL,
`prov_artista` varchar(30) DEFAULT '615c5f7e981debd',
`web_artista` varchar(50) DEFAULT NULL,
`wiki_artista` varchar(256) DEFAULT NULL,
`gen_artista` varchar(50) DEFAULT NULL,
`ncomp_artista` varchar(3) DEFAULT NULL,
`tot_tit_id` int(11) DEFAULT NULL,
`bio_artista` text,
`dsp_image_json` mediumtext,
`nato_artista` date DEFAULT NULL,
`deceduto_artista` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `discografie`
--

CREATE TABLE `discografie` (
`discografie_id` varchar(25) NOT NULL,
`cod_disco` varchar(30) DEFAULT NULL,
`tit_disco` varchar(50) DEFAULT NULL,
`sub_disco` varchar(50) DEFAULT NULL,
`loc_disco` varchar(50) DEFAULT '6130f61ac96743f',
`note_disco` text,
`tipo_disco` varchar(30) DEFAULT NULL,
`dsp_image_disco_json` mediumtext,
`dsp_titoli_id` int(11) DEFAULT NULL COMMENT 'n.titoli in discografia',
`dsp_artisti_id` int(11) DEFAULT NULL COMMENT 'n.artisti in discografia'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `generi`
--

CREATE TABLE `generi` (
`generi_id` varchar(25) NOT NULL,
`des_generi` varchar(50) DEFAULT NULL,
`sotto_generi` varchar(50) DEFAULT NULL,
`note_generi` text
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `lpcdbox`
--

CREATE TABLE `lpcdbox` (
`LPCDBOX_id` varchar(25) NOT NULL,
`cod_lpcdbox` varchar(50) DEFAULT NULL,
`art_lpcdbox` varchar(30) DEFAULT NULL,
`disco_lpcdbox` varchar(30) DEFAULT NULL,
`tit_lpcdbox` varchar(100) DEFAULT NULL,
`form_lpcdbox` varchar(30) DEFAULT NULL,
`genere_lpcdbox` varchar(30) DEFAULT NULL,
`anno_lpcdbox` date DEFAULT NULL,
`anno2_lpcdbox` int(4) DEFAULT NULL,
`anno_p_lpcdbox` int(11) DEFAULT NULL,
`track_lpcdbox` int(11) DEFAULT '0',
`etichetta_lpcdbox` varchar(30) DEFAULT NULL,
`nsupp_lpcdbox` int(11) DEFAULT NULL,
`durata_m_lpcdbox` int(3) DEFAULT '0',
`durata_s_lpcdbox` int(3) DEFAULT '0',
`digit_lpcdbox` varchar(10) DEFAULT NULL,
`giud_lpcdbox` int(2) DEFAULT '6',
`valuta_lpcdbox` decimal(11,2) DEFAULT '15.00',
`bar_lpcdbox` varchar(50) DEFAULT NULL,
`note_lpcdbox` text,
`status_lpcdbox` varchar(20) NOT NULL DEFAULT 'ACQUISITO',
`edizione_lpcdbox` varchar(30) DEFAULT '6143294cc8d9229',
`ddins_lpcdbox` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`dsp_image_lpcdbox_json` mediumtext
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `nazione`
--

CREATE TABLE `nazione` (
`nazione_id` varchar(25) NOT NULL,
`naz_nazione` varchar(50) DEFAULT NULL,
`naz_citta` varchar(50) DEFAULT '[non disponibile]',
`naz_note` text
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `artista`
--
ALTER TABLE `artista`
ADD PRIMARY KEY (`artista_id`);

--
-- Indexes for table `discografie`
--
ALTER TABLE `discografie`
ADD PRIMARY KEY (`discografie_id`);

--
-- Indexes for table `generi`
--
ALTER TABLE `generi`
ADD PRIMARY KEY (`generi_id`);

--
-- Indexes for table `lpcdbox`
--
ALTER TABLE `lpcdbox`
ADD PRIMARY KEY (`LPCDBOX_id`);

--
-- Indexes for table `nazione`
--
ALTER TABLE `nazione`
ADD PRIMARY KEY (`nazione_id`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

[kev1n]

Try changing the joins to:

Code: Select all

RIGHT JOIN artista ON lpcdbox.art_lpcdbox = artista.artista_id
LEFT JOIN nazione ON nazione.nazione_id = artista.prov_artista
LEFT JOIN generi ON generi.generi_id = lpcdbox.genere_lpcdbox
LEFT JOIN discografie ON discografie.discografie_id = disco_lpcdbo

[Mr71]

Ok Kevin...
So it seems to work!!!