asked on

Subquery contains more than 1 row

I have 4 tables that I want to display data from:

anesthmedsgiven

anesthmedsinventory

anestheticrecord

and

patient

table anesthmedsgiven shares column AnestheticRecordNum with table anestheticrecord

table anestheticrecord shares column PatNum with table patient

table anesthmedsinventory shares column AnesthMedName with table anesthmedsgiven

The following query produces the error 'Subquery contains more than 1 row':

SELECT DISTINCT DATE_FORMAT(STR_TO_DATE(DoseTimeStamp, '%m/%d/%Y'),
 '%m-%d-%Y') as 'Date', CONCAT(patient.FName,' ', patient.LName) 
as 'Patient Name', DATE_FORMAT(patient.Birthdate,'%m/%d/%Y') as 'DOB',
 CONCAT(patient.Address,' ',patient.Address2) as 'Address',patient.City,patient.State,patient.Zip,
 anesthmedsgiven.AnesthMedName as 'Anesthetic Medication',anesthmedsgiven.QtyGiven as 'Qty Given 
(mL)',anesthmedsgiven.QtyWasted as 'Qty Wasted (mL)', anesthmedsinventory.DEASchedule as 'DEA Schedule' FROM anesthmedsgiven LEFT JOIN patient ON patient.PatNum =
 (SELECT PatNum FROM anestheticrecord WHERE AnestheticRecordNum = anesthmedsgiven.AnestheticRecordNum) 
LEFT JOIN anesthmedsinventory ON anesthmedsinventory.DEASchedule = 
(SELECT DEASchedule from 
anesthmedsinventory  WHERE (anesthmedsgiven.AnesthMedName = 
'Versed 5 mg/ml' OR anesthmedsgiven.AnesthMedName = 'Valium 5 mg/ml') AND DATE_FORMAT(STR_TO_DATE(DoseTimeStamp, '%m/%d/%Y'), '%m-%d-%Y')) = '07-15-2023'

Open in new window

Here what I want the query output to look like:

I can make the error go away with an ANY in front of the subqueries but that returns every record in the database.

Sorry about the query formatting I couldn't figure out how not to make it not do that.

Thank you for your help.

Pavel Celba

This should provide output w/o the DEA Schedule:

SELECT DISTINCT 
    DATE_FORMAT(STR_TO_DATE(anesthmedsgiven.DoseTimeStamp, '%m/%d/%Y'), '%m-%d-%Y') as 'Date', 
    CONCAT(patient.FName,' ', patient.LName) as 'Patient Name', 
    DATE_FORMAT(patient.Birthdate,'%m/%d/%Y') as 'DOB',
    CONCAT(patient.Address,' ',patient.Address2) as 'Address',
    patient.City,
    patient.State,
    patient.Zip,
    anesthmedsgiven.AnesthMedName as 'Anesthetic Medication',
    anesthmedsgiven.QtyGiven as 'Qty Given (mL)',
    anesthmedsgiven.QtyWasted as 'Qty Wasted (mL)'  
 FROM anesthmedsgiven 
 JOIN anestheticrecord ON anestheticrecord.AnestheticRecordNum = anesthmedsgiven.AnestheticRecordNum
 LEFT JOIN patient ON patient.PatNum = anestheticrecord.PatNum

Open in new window

The above query expects that each row in anesthmedsgiven points to exactly one row in anestheticrecord. Try it and let us know.

The DEA Schedule needs some additional info...
To join anesthmedsinventory on DEA Schedule and the drug name does not look like a correct option. To use drug names and time stamps in the relation also does not make much sense. Do you have NDC or other drug identification in your tables?

I would expect the following:
The table anesthmedsinventory must have better defined relation into the anestheticrecord table and you should use this relation to retrieve DEA Schedule.

ASKER CERTIFIED SOLUTION

William Starck

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer