SQL Delete Issue

[WatsonDyar]

I cannot get this to work:

DELETE tdsReportData.dsReportID, tdsReportData.dsReportDate, tdsIndivData.StaffID
FROM tdsReportData LEFT JOIN tdsIndivData ON tdsReportData.dsReportID = tdsIndivData.dsReportID
WHERE (((tdsIndivData.StaffID) Is Null));

Please help! I'm sure it's something fairly simple.....

 

[Uncle Gizmo]

If you change it to a select statement, what results do you get?

 

[jdraw]

WD,

Do you get an error message???

Your SQL in general is

DELETE  FROM A
LEFT JOIN B
ON A.ID = B.ID 
WHERE
B.StaffID IS NULL

but I could not get it to work.


My sample data

[COLOR="Blue"][B]tblA[/B][/COLOR]   id is PK
id	mname
1	John
2	Mary
3	Ezra
4	Otis
5	Clem
6	Dave
7	Joan

tblB bID is PK id is FK to tblA

bID	id	StaffID
1	1	354
2	2	355
3	3	488
4	4	
5	5	769
6	6	
7	7	878

Tried this to identify records

SELECT tblA.id
FROM tblA 
where tbla.id in 
(SELECT tblB.ID FROM tblB
WHERE tblB.StaffID Is Null);

which gives

id
4
6

And this to do the delete (which works)

DELETE tblA.id
FROM tblA 
where tbla.id in 
(SELECT tblB.ID FROM tblB
WHERE tblB.StaffID Is Null);

Final view of tblA

id	mname
1	John
2	Mary
3	Ezra
5	Clem
7	Joan

 

[WatsonDyar]

UG- The select query will work. From what I could learn, looks like I'm making some typical beginner mistake.

jdraw- let me work through this. I can't thank you enough!

 

[WatsonDyar]

By the way, I'm constantly impressed with the willingness of you all to help out. It's really incredible. Thanks Uncle Gizmo, jDraw, vbaInet , and others for having such amazing help available and the donation of your time!

 

[WatsonDyar]

I'm still having issues. I think it's because the records in tblA that I am trying to delete do not have any corresponding records in tblB

If you have a moment, look at the attached. Using the fdsReportStart form, add a new report using a specific date. Close all and run the fdsReportStart form again using the SAME date.

The problem now is that in the tdsReportdata table, I now have two records for the same date- one with corresponding records in tdsIndivData and one (the one I want to delete) without.

 

[jdraw]

I have looked a little further --at an MySQL sample
http://www.mysqltutorial.org/mysql-delete-join/

[COLOR="Purple"]MySQL DELETE JOIN with LEFT JOIN

You often use LEFT JOIN clause in the SELECT statement to find records that 
exist in the left table and does not have corresponding records in the right 
table. You can also use the LEFT JOIN clause in the DELETE statement to 
delete record in a table (left table) that does not have corresponding record 
in another table (right table).[/COLOR]

NOTE: Only works when there is no corresponding record in the right table.

So in the original case there would be NO RECORD with
tdsIndivData.dsReportID = tdsReportData.dsReportID whether StaffId is NULL or not

I think the bottom line is that, if there is a record in the Right table with FK = PK in Left table, you receive an error.


OOOoops: I see you have posted while I was typing.

 

[WatsonDyar]

Okay, I see now.

Do you have any other ideas on how to automate the delete of the record? Basically, the reports will be available to view in a report object selected by a combo box or menu. I don't want to have records available that have no data!

 

[jdraw]

When you populate your combo, make sure the records you select have the required data.
Said differently, if there are records that don't have all the required data, then don't make them available in the combo.

Rowsource of combo is something like

SELECT fields from Table
WHERE flda is Not Null.....

 

[WatsonDyar]

Okay. Also, I just realized how dumb I am. If I based the combo box on a query of the two tables in an INNER JOIN, I won't get any of the "bad" records I've been so worried about!

Thanks again for your help! GREATLY appreciate it!!

 

[jdraw]

Glad to help.