Missing in SQL Problem
Back to General discussions forum
Dear all, i am trying to solve missing in SQL and i suspect to have the right answer yet the entry is deemed wrong every time.
I have this data:
create table package (pkg_id int, sender text, weight int, trackcode text);
create table attempt (att_id int, pkg_id int, provider text);
create table event (evt_id int, att_id int, status text);
insert into package values (127, 'ann', 582, 'tm2567124');
insert into attempt values (813, 127, 'bl');
insert into event values (4160, 813, 'created');
insert into attempt values (828, 127, 'rps');
insert into event values (4236, 828, 'created');
insert into event values (4414, 828, 'picked up');
insert into event values (4499, 828, 'left at door');
insert into package values (134, 'eve', 420, 'jn8373625');
insert into attempt values (841, 134, 'capost');
insert into package values (140, 'eve', 350, 'es8453740');
insert into attempt values (863, 140, 'dhl');
insert into event values (4581, 863, 'created');
insert into package values (146, 'frank', 730, 'b47976245');
insert into package values (151, 'bob', 420, 'on8645327');
insert into attempt values (882, 151, 'capost');
insert into event values (4691, 882, 'created');
insert into event values (4847, 882, 'at airport');
And the right answer i presume would be: es8453740
This seems to work for all entries even the test entries when is copy them in my SQL sandbox. Yet it keeps telling me that i get it wrong. What is the style if answer expected? Is it only the trackcode or did i get the idea completely wrong and another entry is expected.
Thanks for your help in advance, Best regards,
Lukas
Ah i did the joining wrong and did not see that. Thank you so much, works like a charm now :-)
Help! i was stuck on this problem as well. this is my input data:
create table package (pkg_id int, sender text, weight int, trackcode text);
create table attempt (att_id int, pkg_id int, provider text);
create table event (evt_id int, att_id int, status text);
insert into package values (222, 'claude', 344, 'os9879949');
insert into attempt values (1039, 222, 'sing');
insert into event values (4639, 1039, 'created');
insert into attempt values (1055, 222, 'rps');
insert into event values (4779, 1055, 'created');
insert into event values (4903, 1055, 'picked up');
insert into event values (5065, 1055, 'left at door');
insert into package values (228, 'pete', 285, 'ml4843706');
insert into attempt values (1068, 228, 'capost');
insert into package values (231, 'ann', 200, 'nu7870906');
insert into attempt values (1091, 231, 'bl');
insert into event values (5239, 1091, 'created');
insert into package values (234, 'ann', 144, 'qc4165278');
insert into package values (238, 'frank', 306, 'hr8395311');
insert into attempt values (1113, 238, 'sing');
insert into event values (5327, 1113, 'created');
insert into event values (5421, 1113, 'at airport');
i already checked the data manually one-by-one and pretty sure from what i understand, "ml4843706 nu7870906 qc4165278" should be the correct answer. however, it is wrong when i submitted and it says 'More result than expected'
Does your code consider the following: "all packages, which have no any events, except created"?
Thank you for you reply gardengnome!
I did consider that, which in my case that would be "nu7870906" only, i had done this on my previous attempts but the answer is wrong.
When i checked your answer above to LukasDev, it seems that we need to get packages with no events at all. that's mean ml4843706 and qc4165278 should also be included in the output
Do i have the wrong understanding on this?
Hi, Not every package / attempt has an event associated with it, see for instance the package for Pete in your data. That illustrates that create events do not always exist. Your code then (indirectly) assumes that if an attempt has one event associated with it that it has to be a created event. But given the first observation, is that a safe assumption? Consider the following example:
create table package (pkg_id int, sender text, weight int, trackcode text);
create table attempt (att_id int, pkg_id int, provider text);
create table event (evt_id int, att_id int, status text);
insert into package values (222, 'claude', 344, 'os9879949');
insert into attempt values (1039, 222, 'sing');
insert into event values (4639, 1039, 'NOT_CREATED');