FREE AI-Enhanced Online MySQL Compiler - For learning & practice

Untitled*

You must execute the code again to save those values in the database.

create table darsteller_kf (id int, nconst text, vorname text, name text);
create table kauffilme (id int, tconst text, filmtitel text, imdb_full_actors_list text);
create table film_berufe_cross_tbl (beruf_id int, tconst text, nconst text);

insert into darsteller_kf values (7097, 'nm0856319', 'Duccio', 'Tessari');
insert into darsteller_kf values (3850, 'nm0001553', 'Ennio', 'Morricone');
insert into kauffilme values (7, 'tt0058461', 'Für eine Handvoll Dollar', 'nm0856319');
insert into film_berufe_cross_tbl values (99, 'tt12345', 'nm0856319'); /* Not the same movie as the one we're interested in! */
insert into film_berufe_cross_tbl values (7, 'tt0058461', 'nm0001553');

select 'Initial query:' message;

/* Does not work, as Tessari working on (virtual) movie tt12345 make it return an entry, thus fb.tconst is *not* null. */
SELECT d.id AS darstellerID, d.nconst, d.vorname, d.name, kf.id AS Kid, kf.tconst, kf.filmtitel, fb.beruf_id
FROM darsteller_kf d
LEFT JOIN kauffilme kf ON kf.imdb_full_actors_list LIKE CONCAT('%', d.nconst, '%')
LEFT JOIN film_berufe_cross_tbl fb on d.nconst = fb.nconst WHERE kf.tconst = 'tt0058461' AND fb.tconst is null;

select 'Modified query:' message;

/* Does work, as we ensure to only target fb entries for the same movie *and* for the same person with a double join condition. */
SELECT d.id AS darstellerID, d.nconst, d.vorname, d.name, kf.id AS Kid, kf.tconst, kf.filmtitel, fb.beruf_id
FROM darsteller_kf d
LEFT JOIN kauffilme kf ON kf.imdb_full_actors_list LIKE CONCAT('%', d.nconst, '%')
LEFT JOIN film_berufe_cross_tbl fb on d.nconst = fb.nconst AND fb.tconst = kf.tconst
WHERE kf.tconst = 'tt0058461' AND fb.tconst is null;

message
Initial query:

message
Modified query:

darstellerID	nconst	vorname	name	Kid	tconst	filmtitel	beruf_id
7097	nm0856319	Duccio	Tessari	7	tt0058461	Für eine Handvoll Dollar	NULL