MYSQL data changes when selecting

Question

I am attempting to query a list from a mock database to show when medications are close to expiring. However when it executes it changes some information around and duplicates the key.

    CREATE TABLE IF NOT EXISTS Medbox (
  box_id INT NOT NULL UNIQUE PRIMARY KEY,
  assigned_truck int(4) NOT NULL unique,
  current_status VARCHAR(20) NOT NULL
  );
  

CREATE TABLE IF NOT EXISTS medication (
  med_id varchar(12) NOT NULL PRIMARY KEY UNIQUE,
  box_id INT NOT NULL REFERENCES  Medbox(box_id),
  medication_name VARCHAR(45) NOT NULL,
  medication_exp_date DATE NOT NULL,
  notes VARCHAR(255)
  );
  
  INSERT into Medbox values 
  (101,3316,'Active'),
  (102,3321,'Active')
  ;
  
  
  INSERT INTO medication VALUES
  ("nar001",101,"Narcan",'2021-01-30',null),
  ("nar002",101,"Narcan",'2021-01-30',null),
  ("sal103",101,"Saline",'2020-09-30',null),
  ("sal105",101,"Saline",'2020-08-31',null)
  ;
  select * from Medbox;
  select * from medication;
SELECT CURDATE();


SELECT  med_id,medbox.box_id,medication_name,assigned_truck,medication_exp_date
FROM medbox,medication
WHERE medication_exp_date <= NOW() + INTERVAL 31 day

This results in the following photo: mysql query results

the med_id sal103 is only supposed to be assigned to box 101 with truck 3316 however it adds another entry in box 102 and truck 3321.

Nothing is currently supposed to be assigned to box 102 or truck 3321 however they show that they are after executing.

score 0 · Accepted Answer · answered Aug 31 '20 at 04:48

0

Because you didn't specify the join condition, its doing a cross join.
Your query should have one more where condition like this :

SELECT  med_id,medbox.box_id,medication_name,assigned_truck,medication_exp_date
FROM medbox,medication
WHERE medication_exp_date <= NOW() + INTERVAL 31 day
AND medbox.box_id = medication.box_id

answered Aug 31 '20 at 04:48

CaffeinatedCod3r

821
7
14

Thank you very much! i would upvote but i am still pretty new to the site! It has been about a year since i really touched anything sql. to understand the reasoning better, why would it manipulate the data instead of sending an error? – Nader Aug 31 '20 at 04:58
It is not manipulating the data or there is an error in the query. Its just the way you wrote the query. For a detailed explanation you could see this answer https://stackoverflow.com/a/26317836/9387017 – CaffeinatedCod3r Aug 31 '20 at 05:12

MYSQL data changes when selecting

1 Answers1