Select with SQL browser

[sietwolt]

As a new user I am migrating from ms Access to NuBuilder4 and highly surprised about the possibilities in Nubuilder. Facing some issues:

In a form I want to link a field with Select with 2 other fields in another form. Using the SQL browser gives the following code:

Code: Select all

SELECT
 afspraken.afsp_naam,
    klanten.Klanten_id,
    klanten.kla_klantnaam
FROM
    afspraken
        JOIN klanten ON klanten.Klanten_id = afspraken.afsp_naam AND klanten.kla_klantnaam = afspraken.afsp_naam

After saving/refreshing the pages, the field with select doesn't show a response and remains empty.

What am I doing wrong?

Thanks in advance

[kev1n]

Hi,

The SELECT query for a select object should return two fields/columns:

The first field is the bound value stored in the database.
The second field is the display value shown to users.

[sietwolt]

I turned it in the sql builder as follws:

SELECT
klanten.kla_klantid,
klanten.kla_klantnaam,
afspraken.afsp_naam

FROM
klanten
JOIN afspraken ON klanten.kla_klantid = afspraken.afsp_naam AND klanten.kla_klantnaam = afspraken.afsp_naam


but also no effect .

Please advise what is going wrong

[kev1n]

Are any rows returned when running the query in e.g. phpMyAdmin?

I tested your query (slightly modified) with some dummy tables / data and data is both shown in the select and also saved.

For your reference (generated with ChatGPT)

Code: Select all

CREATE TABLE klanten (
    kla_klantid INT PRIMARY KEY AUTO_INCREMENT,
    kla_klantnaam VARCHAR(255) NOT NULL
);

CREATE TABLE afspraken (
    afsp_id INT PRIMARY KEY AUTO_INCREMENT,
    kla_klantid INT,
    afsp_naam VARCHAR(255) NOT NULL,
    FOREIGN KEY (kla_klantid) REFERENCES klanten(kla_klantid)
);

-- Insert sample data into klanten
INSERT INTO klanten (kla_klantid, kla_klantnaam) VALUES
(1, 'John Doe'),
(2, 'Jane Smith'),
(3, 'Alice Johnson'),
(4, 'Bob Brown'),
(5, 'Charlie White');

-- Insert sample data into afspraken
INSERT INTO afspraken (afsp_id, kla_klantid, afsp_naam) VALUES
(1, 1, 'Consultation'),
(2, 2, 'Haircut'),
(3, 3, 'Follow-up'),
(4, 4, 'Treatment'),
(5, 5, 'Styling');

-- Corrected Query
SELECT
    klanten.kla_klantid,
    klanten.kla_klantnaam,
    afspraken.afsp_naam
FROM klanten
JOIN afspraken ON klanten.kla_klantid = afspraken.kla_klantid;